STATISTICS IO/TIME

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » STATISTICS IO/TIME

11 posts, Page 1 of 2

1 2 »»

STATISTICS IO/TIME

Rate Topic

Display Mode

Topic Options

Author

Message

mah_j

Posted Thursday, October 18, 2012 2:23 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:36 AM
Points: 86, Visits: 984

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.

Post #1374191

Eugene Elutin

Posted Thursday, October 18, 2012 3:05 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:39 AM
Points: 2,556, Visits: 4,398

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.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1374203

mah_j

Posted Thursday, October 18, 2012 4:15 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:36 AM
Points: 86, Visits: 984

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.

Post #1374246

Eugene Elutin

Posted Thursday, October 18, 2012 4:33 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:39 AM
Points: 2,556, Visits: 4,398

mah_j (10/18/2012)

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1374259

Grant Fritchey

Posted Thursday, October 18, 2012 7:34 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1374353

Abu Dina

Posted Thursday, October 18, 2012 9:29 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:34 AM
Points: 494, Visits: 2,158

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?

-----------------------------------
http://www.SQL4n00bs.com

Post #1374437

Eugene Elutin

Posted Thursday, October 18, 2012 9:32 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:39 AM
Points: 2,556, Visits: 4,398

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...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1374443

Grant Fritchey

Posted Thursday, October 18, 2012 9:39 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1374450

Eugene Elutin

Posted Thursday, October 18, 2012 9:43 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:39 AM
Points: 2,556, Visits: 4,398

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...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1374453

mah_j

Posted Saturday, October 20, 2012 12:21 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:36 AM
Points: 86, Visits: 984

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])

Post #1375084

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions