SQLServerCentral / Discuss Content Posted by Christoffer Hedgate / Article Discussions / Article Discussions by Author / Measuring SQL Performance / Latest Posts

RE: Measuring SQL Performance

DBA-640728 — Wed, 08 Oct 2008 08:12:08 GMT

THANK YOU!

RE: Measuring SQL Performance

Grant Fritchey — Wed, 08 Oct 2008 08:08:57 GMT

When you deploy the stored procedures, they'll use the statistics to compile a new plan. If the statistics are maintained well enough for normal purposes, they'll be fine for this. No, I wouldn't say you need to do a special update just because you've tune the queries.That's assuming that you know that your statistic maintenance routines are good.

RE: Measuring SQL Performance

DBA-640728 — Wed, 08 Oct 2008 07:32:47 GMT

thank you, so its not really needed to do an update statistics after these stored procedures parameter sniffing issues has been fixed? would it make any difference? i don't have my update statistics scheduled until the weekend, or should i run it now?

RE: Measuring SQL Performance

Grant Fritchey — Wed, 08 Oct 2008 07:10:53 GMT

That really depends on the system. Most of our systems work just fine with the sampled statistics update, but we have a few tables on a few systems that need an occasional full scan. I wouldn't recommend running full scan all the time, but an occasional use of it sure won't hurt. That assumes you're doing it off-hours if you're going to full scan the entire db.

RE: Measuring SQL Performance

DBA-640728 — Wed, 08 Oct 2008 07:02:25 GMT

hi just a quick question, i was able to identify some stored procedures with parameter sniffing and was able to fix them. After updating this stored procedures, should i do an update statistics with full scan to my DB?

RE: Measuring SQL Performance

DBA-640728 — Mon, 29 Sep 2008 09:05:54 GMT

thank you :)

RE: Measuring SQL Performance

Grant Fritchey — Mon, 29 Sep 2008 07:33:19 GMT

I'd suggest running a trace while the events are occurring. Collect the data out to a file. You can then import it back into a table on a different server and run aggregate queries to identify which procedures are causing the most problems, say the top 10. Focus on them. Get them fixed and running well, then go and collect another trace. Repeat ad infinitum.

RE: Measuring SQL Performance

DBA-640728 — Mon, 29 Sep 2008 07:13:53 GMT

hi but we are talking about many stored procedures, how would i know the ones that have the have the parameter sniffing problem? do i have to check each one? i have a query i found which gives me the number of recompilations, writes, reads, can this query help me find the stores procedures which have this issue?this is the query: [code]SELECT substring(text,qs.statement_start_offset/2 ,(CASE WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,qs.plan_generation_num as recompiles ,qs.execution_count as execution_count ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time ,qs.total_worker_time as cpu_time ,qs.total_logical_reads as reads ,qs.total_logical_writes as writesFROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st LEFT JOIN sys.dm_exec_requests r ON qs.sql_handle = r.sql_handleORDER BY 3 DESC[/code]can i run this query when i am having the performance problem to see which stored procedure/query could be the one with the problems? we are talking about 30-40 transactions per min.

RE: Measuring SQL Performance

Jeff Moden — Sat, 27 Sep 2008 11:12:07 GMT

For starters, you could try adding the "WITH RECOMPILE" option to the sprocs just to test the water. A better way would be to actually assume that parameter sniffing is, in fact the problem, and fix it on the more troublesome code. Last, but certainly not least, it to check the execution plans under different conditions...

RE: Measuring SQL Performance

DBA-640728 — Sat, 27 Sep 2008 07:23:04 GMT

Thanks for your reply, my issue is that I am having major performance issues from time to time the only thing that seems to fix it is running these commands, I think the problem could be parameter sniffing, how would I know for sure?

RE: Measuring SQL Performance

Jeff Moden — Fri, 26 Sep 2008 19:27:41 GMT

Because they clear cache... not just for the proc running, but for everybody. Depending on what's being done, cache is very important for performance by repetative and other code.

RE: Measuring SQL Performance

DBA-640728 — Fri, 26 Sep 2008 13:02:20 GMT

can you please specify why running these commands would not be good to run in a production environment?

RE: Measuring SQL Performance

Jeff Moden — Mon, 25 Dec 2006 12:03:00 GMT

Yep... I realize that my response is a year late, but well done, Chris... Very nice intro to some of the performance measuring tools that are available in SQL Server.

Just a couple of thoughts for everyone, though... having tools without knowing what to expect doesn't really do much...

An example from work... 3 years ago, one of our developers identified a particular stored procedure as a very good candidate for optimization in light of large volumes of data. She very successfully got that process down from 30 minutes to 6 minutes and change. Yippee!! Everyone was happy! Having been recently hired into the company just for such optimizations, they asked me to optimize the same code without me having any knowledge of the recent optimization success of the developer. I got the process down to a little over 4 SECONDS and used less resources to boot!

Am I bragging? Sure, a bit... who wouldn't? But it points out a much larger problem... a lot of developers simply don't know what to expect for performance and, in light of such success as getting a 30 minute process down to 6 minutes, it's hard to convince folks that their perception of performance code needs a little work. They just don't know and having the tools to measure performance without knowing what's actually possible is pretty meaningless. That's where in-house training, mentorship, and performance standards come into play.

Another problem, particullarly in the development phase of a project, is having data to test with. One particular example that comes to mind is one of our developers was asked to write a "grouped running total" based on CustID. Having no data to test against, he used the Northwind DB to test the wonderful correlated subquery he had just been introduced to... Heck, it didn't have a cursor in it so it must be fast, right? His testing showed [him] that it was fast. The code was promoted to production and immediately had performance problems on 1,000 rows... it was taking seconds instead of sub-seconds. When it hit 10,000 rows, it took minutes to run. Millions of rows were expected in the next couple of months.

The problem turned out to be the triangular join (about half a cartesion join) he'd used in the correlated subquery. A lack of data to test against was the killer here.

There's a third, more insideous problem than not knowing what's possible or having enough data to test against. That's TIME... going from 30 minutes to 6 minutes is considered "good enough, next problem please". Managers hover over developers asking "are ya done yet?" Managers and Project Managers, not having a good understanding of why it takes so long, will frequently estimate time for the development of a proc simply by how many blocks the functional diagram (if there is one) has in it. And, of course, everybody, including the developer, wants things done as quickly as possible. What you frequently end up with is code that works great provided there's no real volume of data to work against because the developer didn't take the time to gen test data (pretty easy, actually, if you know what you're doing) or didn't take the time to see if they could do better. The first "good enough" is hardly ever good enough and managers just don't understand how much time is expended fixing performance problems down the road. It's much cheaper to do it right the first time but you need time for that.

Of course, the fourth and most important problem is the whole bloody relationship between management and developers... management wants the code to be done NOW and the developer likes the paycheck they're getting. The developer knows that in the face of a tight schedule, they can write performance challenged code to "get the job done now" and that they'll be given more time later (really good for consultants) to fix the performance problems which, by the way, are percieved as a different set of problems by management. Usually, neither management nor the developers are held accountable for the large expenditure of money and resources it takes to convert performance challenged, crap production code into something that isn't. Again, that's where some performance standards and some volumetric data would come into play.

Measure all you want... have your developers become very skilled at measuring the performance of their code. But, if you don't also train them as to what is possible/expected and actually give them the time to write performance based code, then the performance measuring tools and their results are just so many pretty lights on a Christmas tree... in July.

RE: Measuring SQL Performance

Tom Holden-248075 — Thu, 29 Dec 2005 09:36:00 GMT

DBAs and Developers measuring query performance is important. Indexing and query performance is one area for a DBA to really shine. A couple adjustments and you can be a hero for a day.

Also, if you want to measure many other performance counters on SQL Server/Windows, and many other (IIS, Exchange, network hardware) around the clock and all at the same time, check out http://www.ascendview.com I have purchased this product and have been using it since March 05.

RE: Measuring SQL Performance

Grant Fritchey — Wed, 28 Dec 2005 06:31:00 GMT

When I see words like "heavily indexed" red flags go up. How heavily indexed, and are all those indexes being used (selectivity needs to be as high as possible in order for the query analyzer to use the index, generally above 95%), are you getting excessive bookmark lookups (which double your I/O in lots of cases), are you getting hash joins instead of loop joins (have to evaluate the query plan), are you getting index scans instead of index seeks (another indication that your indexes may be selective enough or your joins are incorrect), does the table have a clustered index, is the cluster placed on the column(s) that will give you the best of chance of avoiding bookmark lookups (leading edge, first column, of the index needs to cluster the data in logical groups that will help speed up the data)... Get all these questions answered, then you can start to look at hardware as a mechanism to speed things up. Worst thing you can do to your queries (write or read) is place a bunch of indexes that are either not used or improperly used by the query optimizer.

Oh, and nice article. It is shocking the number of times you hear that it doesn't matter how fast a query runs on the database. It's good to see that other people are pushing the need for these types of checks as part of query writing. Now if we can just get people to think about the queries being written as they design tables too.

RE: Measuring SQL Performance

Alex-217289 — Tue, 27 Dec 2005 14:02:00 GMT

Though I was aware of all the tools mentioned, I always find myself looking for excuses to not bother. This was a great reminder that SQL provides various very useful tools for the purpose of performance enhancement. Regarding DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE, in my experience, these are useful in a testing/staging environment only. I don't believe you would want to do this in a production environment.

RE: Measuring SQL Performance

Chuck Ritenour — Fri, 23 Dec 2005 05:57:00 GMT

Can we take it a step further, i.e., an article that explains what to do with the results obtained - from both a query rewrite and a hardware POV?I ran a sample query that linked two tables, each with 200million rows, looking to return 1001 records using where 'id' between. I don't think 4.6 seconds is bad, but I always look for better.I'm heavily indexed on both these tables - and have just recently completed a rewrite of the entire process from our front end web search tool to the redesign of the database and all the associated stored proc's. The data gets a update of about 700k records a week, indexing is dropped, records inserted, and indexs reapplied (and statistics re-computed). It works, seems like it generates fast responses - but it takes 6-8 hours to process.Like the others above I found that for short data sets table var's are better, and for temp tables are better for larger returns. My SAN's are normalized (set up to be read/write neutral as opposed to one being faster than the other).My overall questions(based on the results below) areHow do I reduce logical reads, and what does that mean.How do I reduce physical reads.What's a read-ahead - and why did it need 916 when it was 1001 records (it was queried on the primary clustered key - should it not have 'known' via the indexing, statistics and bookmarking and not needed to read ahead).The primary table is a 3rd normal ID only table in it's own database, the secondary table is a loose 2nd normal (lots of text, some id's) in it's own database consisting of one year's data (there are 7 secondary db's).Hope that was enough info to go on.my results:Table 'primary'. Scan count 1001, logical reads 4859, physical reads 6, read-ahead reads 7.Table 'secondary'. Scan count 1, logical reads 1007, physical reads 0, read-ahead reads 916.SQL Server Execution Times: CPU time = 1734 ms, elapsed time = 4675 ms.

RE: Measuring SQL Performance

David.Poole — Mon, 05 Apr 2004 02:05:00 GMT

Thanks Chris,

I've been tuning a stored procedure that itterates down a tree structure to accumulate a list of ids within that structure and then returns the details associated with those ids.

I experimented with temp tables vs table variables and found that some parts of the procedure were faster with temporary tables and others were faster with table variables.

In this scenario I was interested in the overall performance of the procedure.

I spent quite a bit of time with Excel plotting the impact of one vs the other as the tree structure grew more complex.

In the end I decided that for shallow trees table variables were better, but for deep trees it was temporary tables that gained the edge.

RE: Measuring SQL Performance

Chris Hedgate — Fri, 02 Apr 2004 11:34:00 GMT

David: Using those tools I mentioned you will have to sum up the numbers. Ad msuraski said there are other tools that can give a better idea of the entire proc. Personally I am normally more interested in optimizing each of the specific queries inside the proc, but of course you could sometimes go for different approaches for the entire proc and want to check which of them to use.adutton: Very good advice. I thought about including more, but I thought I would lose a little focus with that. But maybe they should have been mentioned. Maybe they can be included in a follow-up sometime, as well as how to simulate production environments on dev servers.

RE: Measuring SQL Performance

Steve Jones - SSC Editor — Fri, 02 Apr 2004 09:50:00 GMT

Great introductory article to looking at performance. The suggestions above are good, but most code might be improved if each developer took a few minutes to run these and see the relative performance of each query v the time for their code to run.

RE: Measuring SQL Performance

Aaron Dutton — Fri, 02 Apr 2004 09:20:00 GMT

It would be nice if you also showed how to clear the sp cache so you can run the results over and over and compare. I think the commands are

DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE

RE: Measuring SQL Performance

msurasky — Fri, 02 Apr 2004 09:16:00 GMT

Hi David:

Use SP:Completed Event in the SQL Profiler... this should be enough for you needs.

RE: Measuring SQL Performance

David.Poole — Fri, 02 Apr 2004 02:54:00 GMT

Good primer!

If I have a stored procedure that does multiple tasks, is there a way of getting the overall cost of the entire procedure or do I have to manually tot up all the costs for each of the individual queries?

Measuring SQL Performance

Chris Hedgate — Thu, 01 Apr 2004 11:23:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/m