meowsh
asked on
Slow procedures, Sort Warnings and Execution Plans
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp
here is another good article for improving performance
here is another good article for improving performance
"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....
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....
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...
Cheers!
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...
Cheers!
I'm guess the proc my be suffering from parameter sniffing.
Brett