CODEWITHSUNDEEP
sqlsql-serverperformance
Andy Macnaughton-Jones
Andy Macnaughton-Jones

Reputation: 1055

Stored procedure performance inconsistancy

We're experiencing a strange scenario when running stored procedures on SQL Server. When running the exact same procedure with the same parameters (we've captured this via SQL Server Profiler) we get very variable CPU usage. Now obviously this depends on the server load and other activities that are going on on the server. However, I'd not expect the variability in "reads" that we are experiencing when running the SP on subsequent occasions - only a few minutes apart.

Day  Hour Min CPU      Reads
70  15  54  4851    33079
70  15  54  5960    33723
70  15  58  5538    30189
70  16  10  5226    29672
70  16  12  24102   1019178
70  16  17  23915   1017621
70  16  17  26348   1018690
70  16  30  6443    28121
70  16  30  6474    28539
70  16  33  5242    27245
70  16  33  6365    27338
70  16  35  5413    27335

Bizzare. Why would we suddenly get a load of reads when we've previously not that then reset themselves. Again I'd say - we have the exact same parameters to this procedure so why it suddenly decides that it's got to do a load of reads is just a bit odd.

Any thoughts on what to look at ? We know that there might be some benefit from some additional queries (Query analyser suggests one for instance) but we wouldn't we expect to see roughly the same number of reads ?

Thanks Andy

Upvotes: 0

Views: 69

Answers (1)

Peter
Peter

Reputation: 38465

Are you running this in production where other persons are running it also? Then it could be a parameter sniffing problem, as the execution plan is compiled for best performance with the parameters supplied when its compiled.

You could try to add With Recompile and see if the problem goes away!

Upvotes: 2

Related Questions