SQL Server Performance Forum – Threads Archive

Queries running much longer than usual

Hello, first of all I have lurked about for some time now. I am very much a beginner in this realm and hopefully I wont come across as a buffoon. I posted this in another forum yesterday and unfortunately I need to figure this out. Anyway, here it is…
I suppose I will run down the past week briefly as it seems to be of relevance. Last tuesday we had some data from one of our tables deleted accidentally (inventory table) and it took quite some time to get that all sorted out. Restored a previous backup and had to merge two backups and delete out some fluff. Anyways, that is all sorted out…painfully and with much sleep loss. However, we have another table (transactions table) that gets hit every half hour on the half hour with a report that trolls through all of our shipment transactions, creates a txt file and ftp’s it to our website. The query used is pretty basic and should (and used to) run very quickly. I ran perfmon to find out which process is causing 100% utilization spikes (that are effectively rendering our server useless for 4.5 minutes every half hour). I discovered sqlservr.exe is the culprit (I was fairly certain at this point however). I also ran profiler to find out which table is causing the issues and which query it was running that was hosing the sql server. The query is a basic select * from transactions where blah blah blah and is relatively small.
The server is up and running 24×7 but is only utilized from 5am to 7pm Monday thru Friday. This process used to take a matter of seconds to run (between 10-20 seconds) and is now pushing 4 minutes. (only noticed this following the backup restore) Now the questions…Hopefully someone can point me in the right direction on this one… Would this problem be linked to the indexing of the transactions table? (it’s the biggest table in this particular db) Would DBCC DBREINDEX() be a good place to start? This table already has a clustered index (the primary key) and the query for the ftp report doesn’t appear to utilize the clustered index. Would a non-clustered index of some of the columns queried during this report speed things up? Would that hinder other running tasks? I suspect when this was all set up initially that the indexes weren’t part of the backup scheme and are lost. I am new to this area and have been thrown to the wolves so to speak. My boss isn’t a DBA and isn’t exactly of great assistance to SQL issues. So I turn to you all for inspiration. Like I said, I’m a newbie to this arena so go easy on me would ya? Thanks in advance.

If you have the query that is used for the report then you should be able to track this down pretty easily. Simply open a SQL Query Analyzer session and paste in your query. Go to the Query menu and turn on Show Execution Plan. Run the query as normal and wait until it is complete. At the bottom of the results you’ll see a table with the Execution Plan. The execution plan is what the system used to process your request. There will be Percentages all over the place for each and every step. You’ll probably see 1%, 2%, 89% or something like that. The 89% part of the process is where the problem lies. As you indicated that it is likely indexes that are missing you’ll most likely see that the 89% of the task says "Table Scan" or "index scan". You can just drag the mouse over the item and you’ll see that it will explain what it did for instance "table scan for table A where field1 = value and field2 = value" etc. You can then simply right click on the item and choose Manage Indexes and add the index for the fields that the query needs in order to process. Once you create the index, you can run the query again and see what is going on. If you are just missing one index as a result of the problems, then you should see something like the above with 1 step being a huge percentage of the time. If you are missing multiples because of joins to other tables or something, you might see 1%, 2%, 33%, 35% or something indicating that you have multiple tables that require attention. Hope this helps

And ,if you canÂ´t understand execution plan, run Index Tuning Wizard (in Query Analyzer) to find indexes for you.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

hi refer BOOKS ON LINE…DOCUMENTATION —-OPTIMIZING DATABASE PERFORMANCE
—QUERY TUNING It may be helpful to u… krishna chaitanya.s
project engineer-SQLDBA

During the less traffic hours on this database run UPDATE STATISTICS on the tables that are involved in that query and as suggested check the execution plan for further assessment. KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;224587 for further information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>