February 24, 2010 at 7:54 am
I have done some searches and decided to just ask.
I have read article after article about indexing tables, and frankly, most have scared me away from putting indexes on my tables in fear of actually slowing down ad hoc queries that we run against them.
But, I have one MAJOR stored procedure that runs against basically one table. And that's the ONLY process that uses that table. That table is large for us (million rows). The SP gets used often and the slow performance is starting to be noticed by users.
Can you please tell me where to start in improving the performance of my SP/T-SQL? Or point me to links/articles that I can read to learn where to start?
Thank you.
Joel
February 24, 2010 at 8:35 am
Indexes are very unlikely to cause read queries to slow. They may not help read queries run faster, but they won't slow them down. Indexes can negatively affect inserts/udpates/deletes because the index may have to be modified as the data changes.
For a place to start... I'd look at Gail Shaw's articles on Simple-Talk.
Basically, if you're trying to tune one query, as opposed to your database, I'd suggest starting with the execution plan for that query. Understanding what's happening behind the scenes will provide you with a basis for how to fix the problem.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 24, 2010 at 8:38 am
First I think there is an important element to consider, indexes really only have costs on the write cycle. For reads like SELECT statements they can usually only help. The issue with indexes is not to go overboard and to create indexes that include columns that are likely to be used in joins and WHERE clauses to filter the data. Don't be afraid of the index, just don't go crazy. Also, in general EVERY table should have a primary key clustered index, while there are few hard and fast rules, this is as close as I usually get.
Keep in mind that performance tuning is as much art as it is science.
In SQL 2005 SSMS there is a function called "Display Estimated Query Plan" If you have a query window open you can get the execution plan graphically for the query in most cases. What this tells you is how the query will be run against the structures. There is a LOT of data there that will not be very understandable, but you want to see it using indexes in the query to satisfy it. If you see lots of Scans mentioned, especially TABLE SCANS it means that it is reading the WHOLE table, now for some little tables it is cheaper to do that than use an index but tables with say 1000 rows probably don't fall into that category.
I'm hoping someone else here has a book recommendation.
CEWII
February 24, 2010 at 8:40 am
Look at the columns in the WHERE / HAVING clauses, and the JOIN conditions. Usually, your more selective columns would be first in the index.
Also, check the master.sys.dm_db_missing_index_xxx views.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply