0

Actually I'm working over a slow running SQL query and I can only access the production database through SSMS. I don't have the direct access to production server or profiler because it's a shared server. Production machine and developement machine both have the same execution plan. But there is huge difference between Statistics Logical Reads and Profiler Reads according to my development machine. So I have to confirm the same difference on the production server.

Through SSMS on Production Server:

I can view STATISTICS IO by writing the following:

SET Statistics IO ON

I can view STATISTICS TIME by writing the following:

SET Statistics TIME ON

I can view Actual Execution Plan by pressing:

CTRL+M

But is there any way to view SQL Server Profiler's Reads in SSMS.

Edit:

Maybe this article will help you understand my problem: INF: Differences in STATISTICS IO, SQL Profiler and Sysprocesses IO Counters (SQL 7 & 2000). Quote: "As a result, in general, the logical reads reported by SQL Profiler do not match up with the sum of those reported by STATISTICS IO. However, the number reported for reads in SQL Profiler should always be equal to or greater than the STATISTICS IO values.".

And in my case there is a huge difference between both of them according to my development machine. And performance is affected over production machine and still shows me very low Statistics Logical Reads on both machines.

Second Edit:

  1. Actual Execution Plan as XML is same on both machines.

  2. Huge means statistics IO are hardly 45 on both machines but Profiler Reads are 4764 on development machine.

Prakash
  • 741
  • 1
  • 7
  • 11
  • In my case there is a huge difference between both of them according to my development machine. And performance is affected over production machine and still shows me very low Statistics Logical Reads on both machines. – Prakash Aug 18 '13 at 06:26
  • @Bogdan Sahlean: Please check the second edit. – Prakash Aug 18 '13 at 12:39
  • I don't have access to profiler on production server that's why I had asked this question. – Prakash Aug 18 '13 at 13:30
  • 1
    Yes, I can retrieve the actual execution plan from both the machines because I have access to SSMS on both machines. And will soon update the question with that. – Prakash Aug 18 '13 at 14:11
  • What is the actual root problem you are trying to solve here? Is it large query duration? If so can you reproduce this on your development machine? If you can't reproduce this on your development machine then why do you think the discrepancy in reads (that does appear in the dev environment) have got anything to do with it? You may be bottle necked on a number of different resources. See [Performance Tuning Waits & Queues](http://technet.microsoft.com/en-us/library/cc966413.aspx) for an approach you can use. – Martin Smith Aug 18 '13 at 16:16
  • @Martin Smith: Yes, I'm able to reproduce it on the development machine. Other queries with low reads according to development machine executes rapidly over the production machine. And don't you think that a query having reads 4764 is a bottleneck in it's own? – Prakash Aug 18 '13 at 17:00
  • 1
    @Prakash - Reads is only one metric. `SELECT TOP 1 COUNT(*) OVER() FROM master..spt_values` shows up as over 5,000 reads but completes in `4 ms` on my machine. What is the actual query duration you are getting? And the query? – Martin Smith Aug 18 '13 at 17:14
  • @MartinSmith: Duration: CPU time = 513 ms, elapsed time = 1287 ms for the second query in this [question](http://stackoverflow.com/questions/18258031/reads-are-not-getting-low-after-putting-a-index). – Prakash Aug 19 '13 at 07:43

0 Answers0