Start a Conversation

Unsolved

This post is more than 5 years old

A

5 Practitioner

 • 

274.2K Posts

345

June 28th, 2013 02:00

SQL Stats collection Workflow

Hi

I have a customer who is running SQL stats collection for the SQL Query Optimizer process, however it's driving large IOPS (25,000 READ IOPS on a database that is 800GB in size, and see's normal transaction workloads of around 1500 IOPS total (read and write). At the moment the database is on SSD's on a server, so the high reads from the Query Optimizer routine are no problem.

I'm not an SQL Server expert, however via some googling I've got a reasonable idea of what the Query Optimizer does (and why the stats collection is run), however I don't have an understanding of the workflow process involved. When I say workflow, I mean is the stats collection process sequential or random?

Also, does the stats collection process only touch a block of data once, and then move onto the next block of data (irrespective of whether it's random or sequential workload)?

What I'm trying to work out is whether cache (FAST cache, XtremSW Cache, etc), would be of any benefit for this process, or whether the reads will have to be delivered from spinning disk alone (due to a cache unfriendly nature of the stats collection process).

Thanks

No Responses!
No Events found!

Top