Several SQL Server Performance Tuning how to's
Note: This blog post is from 2008. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.Need to solve problems with SQL Server performance? Here are several resources that can help:
- How to troubleshoot SQL Server performance issues
- How to troubleshoot application performance issues
- HOW TO: Troubleshoot Application Performance with SQL Server
- How to troubleshoot slow-running queries on SQL Server 7.0 or on later versions
- How to troubleshoot the performance of ad-hoc queries
- Troubleshooting stored procedure recompilation
- Understanding and resolving SQL Server 7.0 or 2000 blocking problems
- How to monitor blocking in SQL Server 2005 and in SQL Server 2000
- How to resolve blocking problems that are caused by lock escalation in SQL Server
- How to capture error messages that are sent to clients from an instance of SQL Server
- How to determine proper SQL Server configuration settings
Some of these offer knowledge and understanding, others offer specific steps to follow. Most offer links to still other resources (including often specific entries in Books Online).
Note that some may be written more to those still running SQL Server 7 (less likely) or SQL Server 2000 (not so unlikely), though many do cover SQL Server 2005 as well. Just keep this in mind while reading, both if some step doesn't seem to follow, and also in case it may be that some setting that suits one release may not suit another. In fact, some of the resources specifically discuss how things have changed in later releases, and how in fact some settings or techniques for older releases may be very different for later ones. All this just calls for discernment while you read.
These are all in addition to a couple of entries I wrote back in April (starting here) on some other advanced tools and techniques for diagnosing SQL Server problems.
Sometimes CF gets blamed for problems when in fact the problem is in the DBMS--and it's not always a problem due to the SQL being sent from CF. Sometimes the same code can run very differently one time than another. In that case, you really need to understand why this is happening. I hope the resources above may help you. If you ever want direct assistance, this is one of the things I help people with in my available consulting.
For more content like this from Charlie Arehart:Need more help with problems?
- Signup to get his blog posts by email:
- Follow his blog RSS feed
- View the rest of his blog posts
- View his blog posts on the Adobe CF portal
- If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
- See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed
http://web.archive.o...
And it offers a link to the download (the link to the help file offered doesn't work, but perhaps it's embedded in the EXE as well.) I've not yet installed it but hope to soon.
It's interesting that you thought to point out a monitoring tool here. I didn't really mean for the entry to go down that road (I was focused on information resources). Still, thanks.
And I'll also point readers to a section of my CF411 site where I do indeed list several other Database Monitoring Tools:
http://www.cf411.com...
So many CF shops either have no DBA, or the one they have doesn't talk much with the CF folks. We can't expect to turn every CFer into a DBA, and naturally things can differ quite a bit among the various DBMS's, but there are some essentials that most folks should be able to take in, and they'd do well to understand. That's what motivated me to write this.
But sure, there's a whole lot more we could talk about, regarding hardware, software, network config--and all just in the guise of the database, in addition to CF. This entry isn't the place for this, but Mike and many other CFers out there often tarry down the lanes of these worlds, and those interested in troubleshooting and tuning should definitely check out their blogs.