July 2, 2014 at 4:38 am
Hi all
I was wondering where people start when tuning their SQL Servers, do people start by looking at their wait stats and from there finding the worse performing queries in the plan cache? Or do people collect a workload using profiler\extended events, and what kinds of things do they collect in this workload.
I know its a pretty vague question but any approaches you use are welcome.
Thanks
July 2, 2014 at 5:07 am
All performance tuning should start at the server. Ensure the server is well-configured first. Then start gathering metrics, again, at the server level to start. Wait stats, etc. Be sure things are relatively stable and well-configured there before you get to query tuning. Then, as far as query tuning goes, there are two approaches, and it depends on the time I have and the importance of the server which I'm going to use. Quick & inaccurate, query sys.dm_exec_query_stats to get the worst performers from there. Slower, more intensive and very accurate, use extended events to capture query metrics and then roll that data up to get the worse performers. From there, tune the worst performers. Wash, rinse and repeat. But it's really important to get a good baseline of metrics on the server before you start trying to tune queries. You need to know that you have a stable platform.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2014 at 5:36 am
Hi Grant
I actually have your book SQl Server 2012 Performance Tuning. The flow of the book seems to be arming the reader with techniques for tuning queries followed by a capture of the workload at the end. In it you say capturing rpc_completed and sql_batch_completed events are all thats needed for capturing a workload, is it really that simple?
After capturing this I would determine the costliest queries using the gathered wait stats as the guide for what to look for, is this a good approach?
Thanks
July 2, 2014 at 6:04 am
1) Monitor regularly for long-duration blocking and deadlocks. Common "performance" problems that are often easily fixed
2) One of my best tools is aggregate Profiler analysis. The free Qure tool from DBSophic.com is incredibly helpful for that. The piece of code that runs for 15 minutes could be a completely useless tuning exercise. But shaving 0.05 seconds off of the sproc that only takes 0.2 seconds to complete could be a MASSIVE improvement for the box if that sproc gets called 40 times per second. Aggregate profiler analysis lets you see these things.
3) Every application on every system should have an Indexing Strategy session performed. This is expert-level stuff though but can pay incredible dividends
4) ALWAYS review your server, IO subsystem and sql server and database and connectivity configuration!!! Almost no one takes care of the details there, and there are some DISASTROUSLY bad defaults and common configurations!
5) Very few shops have proper maintenance set up either. Obviously index/statistics mx is the key thing for performance tuning.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 2, 2014 at 6:24 am
Kwisatz78 (7/2/2014)
Hi GrantI actually have your book SQl Server 2012 Performance Tuning. The flow of the book seems to be arming the reader with techniques for tuning queries followed by a capture of the workload at the end. In it you say capturing rpc_completed and sql_batch_completed events are all thats needed for capturing a workload, is it really that simple?
After capturing this I would determine the costliest queries using the gathered wait stats as the guide for what to look for, is this a good approach?
Thanks
Well, not sure I'd call that simple. It's a heck of a lot of data you're going to capture. You need to be prepared to deal with that in some fashion. But yeah, if we're talking about understanding which queries are running long, called more frequently, use more resources, that's all you need. But, there are all sorts of other issues that you may also need to deal with; blocking, deadlocks, resource contention. All these may require additional monitoring. And, I'd listen to Kevin too. The man knows what he's talking about.
As for the book, Chapters 1 & 2 (Chapters 1-6 in the new version) talk about setting up monitoring first, server and query metrics. Then we go through all the query tuning chapters. I have an example of capturing the metrics and then tuning the queries at the end of the book, yes. But the book is oriented, I hope, the same way as I said above, server first, then queries & all the rest.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2014 at 7:07 am
Thanks to you both, I didn't know about the Qure tool so will take a look at that, and thanks for pointing me in the right direction.
In regards server level configuration do you know any good resources for recommended settings?
Thanks
July 2, 2014 at 7:25 am
My SQL Saturday Performance Tuning Precon? I am also building a new session entitled "SQL Server Defaults SUCK!!" 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 2, 2014 at 7:33 am
yeah, I don't have a precise checklist that I use. I'm not sure of one either. Brad McGeehee may have one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2014 at 7:53 am
Kwisatz78 (7/2/2014)
In regards server level configuration do you know any good resources for recommended settings?
To be honest, most things I would leave at default unless I have a good reason to change them.
Ones I will change:
Max server memory - see chapter 4 of Troubleshooting SQL Server (will post URL later)
Cost threshold for parallelism - increase it. 5 is stupidly low.
Optimise for ad-hoc - enable, has almost no downsides.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2014 at 8:01 am
GilaMonster (7/2/2014)
Kwisatz78 (7/2/2014)
In regards server level configuration do you know any good resources for recommended settings?To be honest, most things I would leave at default unless I have a good reason to change them.
Ones I will change:
Max server memory - see chapter 4 of Troubleshooting SQL Server (will post URL later)
Cost threshold for parallelism - increase it. 5 is stupidly low.
Optimise for ad-hoc - enable, has almost no downsides.
Definitely concur with those 3. Quite a few more than that though. Maxdop, High Power setting, numerous associated with the IO subsystem (regardless of type). Many/most SQL Servers are or are now being put on VMs and there are quite a few ways to get screwed there too.
One of the worst two settings are actually database-level. Data file and log file growth factors (as well as initial database/log file sizes).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 2, 2014 at 8:51 am
Well I am glad you are talking about things I generally look at when configure a SQL server instance, makes me feel I am on the right track.
In regards your precon Kevin where is that? I am at SQLBits in a couple of weeks which is usually good for this kind of thing.
July 2, 2014 at 9:04 am
Kwisatz78 (7/2/2014)
Well I am glad you are talking about things I generally look at when configure a SQL server instance, makes me feel I am on the right track.In regards your precon Kevin where is that? I am at SQLBits in a couple of weeks which is usually good for this kind of thing.
Speaking of precons, I'll be doing a query tuning precon at SQL Saturday Albany in July, SQL Connections in Vegas in September and PASS Summit in Seattle in November. I'll be doing a precon just on Execution Plans in October in Belgium at SQL Server Days.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2014 at 9:11 am
Unfortunately for the SQL Saturday (and other conference precons) you have to be there. Some may be able to be downloaded if you pay, but not sure about that.
Hey, now that I think about it, Brent Ozar has a very good sql server server config checklist: http://www.brentozar.com/first-aid/protected/?amazing-posters-whitepapers/Brent-Ozar-Unlimited-SQL-Server-Setup-Checklist.pdf
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 2, 2014 at 9:43 am
TheSQLGuru (7/2/2014)
High Power setting, numerous associated with the IO subsystem (regardless of type). Many/most SQL Servers are or are now being put on VMs and there are quite a few ways to get screwed there too.
I was thinking of SQL config settings only, not OS/hardware ones
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2014 at 10:20 am
TheSQLGuru (7/2/2014)
Unfortunately for the SQL Saturday (and other conference precons) you have to be there. Some may be able to be downloaded if you pay, but not sure about that.Hey, now that I think about it, Brent Ozar has a very good sql server server config checklist: http://www.brentozar.com/first-aid/protected/?amazing-posters-whitepapers/Brent-Ozar-Unlimited-SQL-Server-Setup-Checklist.pdf
Great stuff I will have a read of that also
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply