October 18, 2012 at 2:23 am
Hi all
I have a query and i get alot time out in a day
i check that with tuning advisor and it gave me an index on Transaction table.
i SET STATISTICS IO and STATISTICS TIME ON and once i checked the query without index, and once with create index
i checked them in a test database(smaller size),i want to know if i can trust the information and create index in primary database?logical reads are less with new index.is Logical reads effective in having better performance?
this is the result:
--with index
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1000 row(s) affected)
Table 'Invoice'. Scan count 0, logical reads 3036, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Transaction'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 523 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
--without index
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1000 row(s) affected)
Table 'Invoice'. Scan count 0, logical reads 3036, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Transaction'. Scan count 1, logical reads 29657, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 636 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
October 18, 2012 at 3:05 am
The first thing I would like to advise you when you are gathering statistics in such way is to clean cache before each execution using following command:
DBCC FREEPROCCACHE
Now about reads.
Yep, number of reads does indicate difference in performance. But it's not so straight forward as it appears.
As you know there are two types of reads mainly considered when reviewing performance stats: Physical and Logical.
Logical read happens every time when SQL engine requests the page from the buffer cache. Physical read will happen when required page is not in cache, so engine need to place it to the buffer cache by reading it from disk.
Now numbers. Here you will really need to look a query plans (but you didn't even post a query itself). Let say you join two tables together and you have no index on columns you join on (let say it is called as "ID". If you check query plan, you very likely will find Hash Match join of two tables (index) scans. What SQL needs to do for this? It will need to access "ID" from each joined table only once, to do so it will read only once all pages from both tables: once but ALL. You would expect number of logical reads (if all data is in buffer) to be sum of number of pages from both tables. et Say 100 pages + 300 pages will require just 400 logical reads. Does it looks reasonable?
Now you add an index on ID column and check the plan. I will guarantee you will see Nested Loop join with index seeks and IO stats will show much higher logical reads. Why? Because each row (from filtered by Index Seek set) had to be 'seeked' individually, so the same pages will need to be accessed multiple times, each access will count as a logical read. As a result, you get total number of logical reads much higher, but "Index Seek" will pre-filtered sets, so engine will not need to scan whole tables and Nested Loop join is much less expensive (it requires much less CPU work) than Hash Match and working on pre-filtered sets will definitely reduce memory used for processing.
So, lower number of Logical Reads alone doesn't really guarantee better performance.
You should pay more attention to number of Scans and Physical reads.
October 18, 2012 at 4:15 am
the most conflicts faced by me is key lookup in most of queries that i get time out.
also there is a job that updates statistics of a table every night,and i think i am facing parameter sniffing.i suggest using option recompile ,but i think it is better to omit key lookup from the plan.but if i create index with include for all my queries that have time out, i afraid of Loss of memory.
October 18, 2012 at 4:33 am
mah_j (10/18/2012)
the most conflicts faced by me is key lookup in most of queries that i get time out.also there is a job that updates statistics of a table every night,and i think i am facing parameter sniffing.i suggest using option recompile ,but i think it is better to omit key lookup from the plan.but if i create index with include for all my queries that have time out, i afraid of Loss of memory.
The above has nothing to do with Logical Reads...
Yes, you are always "facing parameter sniffing" when you use parameters. That is the way which allows SQL to re-use optimised query plans. Sometimes this feature may cause the problems but OPTION RECOMPILE is not panacea solution to get around it. Do you really want your proc to recompile every its execution? There are another ways to get around problems caused by parameter sniffing. Don't use parameters directly, declare local variables, read parameters into them and use them in your queries. It will make SQL to generate more generic plan... But you do all of above if you really have a problem, it should not be used as universal solution for all your queries/procs.
To get away from Key Lookups, have you tried to include all required columns into index, using INCLUDE?
http://msdn.microsoft.com/en-us/library/ms190806(v=sql.105).aspx
October 18, 2012 at 7:34 am
Why would you not trust the numbers? They're a good measure for performance. Understand, when you get to really complex queries, those measures actually can cause a performance hit. It's better to use Extended Events to capture query metrics with the most minimal impact on the system.
As to the advice about clearing the cache, make sure you're on a test system only. That will impact a production server in a negative way. Also, it's good for measuring compile time, but in general I don't use it. Instead I run the query 3-4 times and then take the average execution time & reads (although they shouldn't change much) as the measure of how quickly things ran.
As to bad parameter sniffing, you need to look at the distribution of data within your statistics and then look at values for the compiled plan to see if the value used falls within the good or bad distribution of your statistics (assuming you have bad distribution). Then you need to determine the best way to solve it. As was stated, recompile works, but isn't the best alternative in all situations. You can also use OPTIMIZE FOR query hint, local variables, simply keeping the statistics up to date. I actually cover all this in detail in a chapter in the SQL Server MVP Deep Dives Volume 2 book (all proceeds go to charity).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 18, 2012 at 9:29 am
the most conflicts faced by me is key lookup in most of queries that i get time out
Could this be considred a good hint to add some covering indexes?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
October 18, 2012 at 9:32 am
Abu Dina (10/18/2012)
the most conflicts faced by me is key lookup in most of queries that i get time out
Could this be considred a good hint to add some covering indexes?
It depends...
October 18, 2012 at 9:39 am
Abu Dina (10/18/2012)
the most conflicts faced by me is key lookup in most of queries that i get time out
Could this be considred a good hint to add some covering indexes?
Not automatically, no. It depends on how much that's really hurting performance. Because modifying existing indexes or adding new ones both come with overhead.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 18, 2012 at 9:43 am
Grant Fritchey (10/18/2012)
Abu Dina (10/18/2012)
the most conflicts faced by me is key lookup in most of queries that i get time out
Could this be considred a good hint to add some covering indexes?
Not automatically, no. It depends on how much that's really hurting performance. Because modifying existing indexes or adding new ones both come with overhead.
Having Key Lookup's may not be the reason behind queries timing out...
October 20, 2012 at 12:21 am
Thanks to all for your helpful suggestions.
EXEC sp_executesql
N'SELECT TOP (1000) [t0].[ReferenceNum], [t0].[TDate], [t0].[Counter], [t0].[Success], [t0].[Kind], [t1].[Amount], [t1].[UID], [t1].[INumber], [t1].[IDate], [t1].[State]
FROM [dbo].[Trans] AS [t0]
INNER JOIN [dbo].[Invoice] AS [t1] ON [t0].[UID] = ([t1].[UID])
WHERE ((([t0].[Success] = @p0) AND ([t1].[State] <> @p1)) OR (NOT ([t0].[Success] = 1))) AND ([t0].[Kind] = @p2) AND ([t0].[Date] >= @p3) AND ([t0].[Date] <= @p4) AND ([t1].[ID] = @p5) AND ([t1].[TID] = @p6)
ORDER BY [t0].[Date] DESC ',
N'@p0 int,@p1 int,@p2 int,@p3 datetime,@p4 datetime,@p5 int,@p6 int',
@p0 = 1,
@p1 = 2,
@p2 = 1003,
@p3 = '2012-07-22 13:05:11',
@p4 = '2012-10-17 13:05:13',
@p5 = 1024,
@p6 = 1
this is the way my query runs,i get it from sql profiler.
i ran DBCC FREEPROCCACHE and one time executed the query with these parameters and next time with another parameters ,Both times it used one plan,and for one of them it took a long time but when i used OPTION(OPTIMIZE FOR UNKNOWN) it ran in just 4 seconds.(in test database)
but likewise i have key lookup,and parallelism is added and plan offers to create an index.also logical reads decrease
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Trans] ([Kind],[Date])
INCLUDE ([ReferenceNum],[UID],[Success],[Counter])
October 20, 2012 at 7:24 am
...and plan offers to create an index.also logical reads decrease
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Trans] ([Kind],[Date])
INCLUDE ([ReferenceNum],[UID],[Success],[Counter])
Then go for it!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply