Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Slow procedures, Sort Warnings and Execution Plans

Posted on 2004-10-07
6
Medium Priority
?
312 Views
Last Modified: 2008-02-01
Experts,

One of my customers (a bank) has a dual processor 4GB database server that, at random times, exhibits extremely slow performance when running one particular procedure.  The database is no more than 8GB in total size so they have enough memory to absorb 50% of their data at a time.  
They are looking to me to resolve their performance.

I have run a basic SQL Profile and the trace reveals, that at the time in question, the proc required a long duration (several minutes!) and gerenated enormous number of reads.  The trace also reports multiple sort warnings although they are coming from different SPIDs which suggests the sort warnings are knock on effect of the rogue proc rather than from the proc itself.

We have extracted the long-running procedure calls from the trace, and when executed via Query Analyser, the proc runs fine.  The execution plan reveals nothing obvious i.e it uses mostly index scans/seeks and no table scans.

I have also checked that the indexes in their live environment match that of our product specification.  All relevant indexes appear to be in place and the indexes are regularly maintained.

So I'm a bit stuck on how to proceed and would like some advice.  Given that they are a bank and this is their live environment, they will be reluctant to release a copy of the database to us.  Hence I am looking for some further diagnostics steps that they can run of the live database

Please help...I'm getting pressure to resolve this.
Thanks
Meowsh
0
Comment
Question by:meowsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 12252429
Post the procedure.  Lots of time, the SQL will run faster in Query Analyzer because you don't use parms and just execute the SQL.

I'm guess the proc my be suffering from parameter sniffing.

Brett
0
 
LVL 34

Accepted Solution

by:
arbert earned 750 total points
ID: 12252435
0
 
LVL 15

Expert Comment

by:justinbillig
ID: 12252466
when was the last time thier indexes were rebuild.

Do they do alot of heavy functioning like lots of casting, or substrings, or any other functions like that.

are they using the right datatypes on all thier columns? integer searching / sorting / joining is the fastest.

and the main question ( which i probably know the answer to )

Is there database normalized? Do they have redundant data or repeating groups?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:justinbillig
ID: 12252491
0
 
LVL 34

Expert Comment

by:arbert
ID: 12252536
"when was the last time thier indexes were rebuild.

Do they do alot of heavy functioning like lots of casting, or substrings, or any other functions like that.

are they using the right datatypes on all thier columns? integer searching / sorting / joining is the fastest.

and the main question ( which i probably know the answer to )

Is there database normalized? Do they have redundant data or repeating groups?"


If it runs fast in analyzer but not the proc, probably not a structure problem....
0
 
LVL 18

Expert Comment

by:mdougan
ID: 12253960
Hi meowsh,

Some other thoughts... sometimes, intermittent performance problems can result from things such as  not running statistics on tables that have a high degree of inserts/updates/deletes.... or, the production database does not have the same indexes which exist on the development database.... or, the area where the data files are located for the temp db are low on available drive space... this causes lots of paging and can really tank the execution of an innocent query.  Another problem can result from locks... so, when experiencing the performance  hit, take a look at Current Activity, and see what locks are being  held... we've added  the  WITH (NOLOCK) to almost all of our Select statements which only retrieve data for read only access, as that prevents blocking locks on the tables which are used by many other procedures.

Cheers!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question