Performance Degradation

We have a view in production that have been working fine. Recently, the performance on it has changed signficantly. The view is "union" (not "union all") of select statements on four other views. In the past, it would take a few minutes to return the resultset back, but now, it's taking like 30+ minutes.

The individual select statements only take 1 minutes, 2 minutes, 4 minutes and 9 minutes respectively. But when you run the overall select statement with the unioning of the 4, it takes 30+ minutes. This shows the tempdb resources needed to execute the statement is taxing. When we changed the union to union all, the statement only took 13 minutes to run.

Over the weekend, a decimal field was widened from 9,2 to 11,2. Replication was turned off before the field change and then turned back on after the changes to the field. (I hope I have that replication explained right. I'm not familiar with replication as a process.) There was mention that any custom indexes might have been overwritten/lost due to the replication.

The DBA reindexed all the underlying tables for the views tonight.

My question is if the reindexing doesn't improve the performance. Where else can we check? What else can we do?

Check statistics? Check the transaction log/drive? Does calling a view cause impact on the transaction log? Another thought would be to place indexes on the views. We don't have any in place at the moment.

Unfortunately, I can't post the TSQL due to company rules.

Any ideas to improve the performance would be greatly appreciated.

KenReindexing is a good start. Next you might want to take a look at the query plan. Perhaps, you do not have appropriate index.

Also, if you do not need filtering, consider using 'union all' instead of just 'union'. When you only specify 'union', the system will have to filter out duplicates data. Thus, increase overhead.

Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000

In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David
|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan.
>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>

Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000

In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds
,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan.
>
>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>

Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000

In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan:).
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003, using
>> 4
>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
>>Did you update the stats after the restore on the dev machine? If not the
>>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003,
>> using
>> 4
>>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

performance bottleneck

I am looking at the performance of one of our production servers and have
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.184
1) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly'?
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>
|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:

> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly'?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>
>
|||Hi
"msnews.microsoft.com" wrote:

> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John

performance bottleneck

I am looking at the performance of one of our production servers and have
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.1841) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly''
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:

> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly''
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>
>|||Hi
"msnews.microsoft.com" wrote:

> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/downl...aits_Queues.doc
John

performance bottleneck

I am looking at the performance of one of our production servers and have
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.1841) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly''
--
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:
> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly''
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
> >I am looking at the performance of one of our production servers and have
> >done Perfmon. I am seeing a definite bottleneck on the following drives
> >
> > I, L, H and J
> >
> > What do you thing and is there anything else I am missing here.
> >
> > Thanks for your help.
> >
> >
> >
> > PhysicalDisk
> > E
> > T
> > O
> > I
> > L
> > H
> > J
> >
> > % Disk Time
> > 0.693
> > 56.483
> > 1.917
> > 379.04
> > 326.234
> > 470.186
> > 149.234
> >
> > Avg.Disk Queue Length
> > 0.007
> > 0.565
> > 0.019
> > 3.79
> > 3.262
> > 4.702
> > 1.492
> >
> > Current Disk Queue Length
> > 0.01
> > 0.181
> > 0.014
> > 27.186
> > 2.664
> > 3.443
> > 2.935
> >
> > Disk Writes/sec
> > 1.234
> > 112.324
> > 22.064
> > 5.734
> > 5.535
> > 8.989
> > 4.184
> >
> >
> >
>
>|||Hi
"msnews.microsoft.com" wrote:
> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John

Performance ?

If I set the performance monitor alert on the production machine so is there
any performance issue ?
Hi,
No issues, But select only required counters. See the below site, it defines
about lots of good monitoring counters
http://www.sql-server-performance.com
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:E4CA1ECE-8015-4186-8CAB-3315CBE32474@.microsoft.com...
> If I set the performance monitor alert on the production machine so is
> there
> any performance issue ?

Performance

Hi,
Our production server is running very slow. A query on Staging server takes
less than 1 minute is taking more than 8 minutes on Production. It's the same
with all the databases on Production server. I have looked and found that
there is no blocking. What's the best way to troubleshoot ? Thanks.
Start with comparing the execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> Hi,
> Our production server is running very slow. A query on Staging server takes
> less than 1 minute is taking more than 8 minutes on Production. It's the same
> with all the databases on Production server. I have looked and found that
> there is no blocking. What's the best way to troubleshoot ? Thanks.
|||The best way is to get an expert in to give you a quick performance review,
and then mentor you on how to be better at it yourself. You could waste
days or weeks checking this and that as recommended by forum posters but a
consultant could nail it down in a few hours or even minutes.
TheSQLGuru
President
Indicium Resources, Inc.
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> Hi,
> Our production server is running very slow. A query on Staging server
> takes
> less than 1 minute is taking more than 8 minutes on Production. It's the
> same
> with all the databases on Production server. I have looked and found that
> there is no blocking. What's the best way to troubleshoot ? Thanks.
|||I did just that and the execution plans are exactly same. The jobs for
optimizing and defargging the indexes are fine and running as scheduled. The
only difference between the two servers is that the Staging is on the Local
Network and the Production is at a different location. Could it be the
network pipe causing the delay?
"Tibor Karaszi" wrote:

> Start with comparing the execution plans.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
>
|||It could. It depends on your query. If your query returns a large
dataset, or if your query isn't actually a query, but a batch with a
loop or cursor. In that case, make sure you SET NOCOUNT ON at the start
of your batch.
Gert-Jan
sharman wrote:[vbcol=seagreen]
> I did just that and the execution plans are exactly same. The jobs for
> optimizing and defargging the indexes are fine and running as scheduled. The
> only difference between the two servers is that the Staging is on the Local
> Network and the Production is at a different location. Could it be the
> network pipe causing the delay?
> "Tibor Karaszi" wrote:
|||You are right, it is returning a large data set (> 600,00 rows). The
difference is just a second for queries returning 5000 rows.
"Gert-Jan Strik" wrote:

> It could. It depends on your query. If your query returns a large
> dataset, or if your query isn't actually a query, but a batch with a
> loop or cursor. In that case, make sure you SET NOCOUNT ON at the start
> of your batch.
> Gert-Jan
>
> sharman wrote:
>
|||I've seen cases where AV programs will AV the named pipes used between the server and the client.
So, if the client connects using Named Pipes, you want to check this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:68E0A5EA-9B90-4A24-A0BF-754E5A96B530@.microsoft.com...[vbcol=seagreen]
> You are right, it is returning a large data set (> 600,00 rows). The
> difference is just a second for queries returning 5000 rows.
> "Gert-Jan Strik" wrote:

Performance

Hi,
Our production server is running very slow. A query on Staging server takes
less than 1 minute is taking more than 8 minutes on Production. It's the sam
e
with all the databases on Production server. I have looked and found that
there is no blocking. What's the best way to troubleshoot ? Thanks.Start with comparing the execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> Hi,
> Our production server is running very slow. A query on Staging server take
s
> less than 1 minute is taking more than 8 minutes on Production. It's the s
ame
> with all the databases on Production server. I have looked and found that
> there is no blocking. What's the best way to troubleshoot ? Thanks.|||The best way is to get an expert in to give you a quick performance review,
and then mentor you on how to be better at it yourself. You could waste
days or weeks checking this and that as recommended by forum posters but a
consultant could nail it down in a few hours or even minutes.
TheSQLGuru
President
Indicium Resources, Inc.
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> Hi,
> Our production server is running very slow. A query on Staging server
> takes
> less than 1 minute is taking more than 8 minutes on Production. It's the
> same
> with all the databases on Production server. I have looked and found that
> there is no blocking. What's the best way to troubleshoot ? Thanks.|||I did just that and the execution plans are exactly same. The jobs for
optimizing and defargging the indexes are fine and running as scheduled. The
only difference between the two servers is that the Staging is on the Local
Network and the Production is at a different location. Could it be the
network pipe causing the delay?
"Tibor Karaszi" wrote:

> Start with comparing the execution plans.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
>|||It could. It depends on your query. If your query returns a large
dataset, or if your query isn't actually a query, but a batch with a
loop or cursor. In that case, make sure you SET NOCOUNT ON at the start
of your batch.
Gert-Jan
sharman wrote:[vbcol=seagreen]
> I did just that and the execution plans are exactly same. The jobs for
> optimizing and defargging the indexes are fine and running as scheduled. T
he
> only difference between the two servers is that the Staging is on the Loca
l
> Network and the Production is at a different location. Could it be the
> network pipe causing the delay?
> "Tibor Karaszi" wrote:
>|||You are right, it is returning a large data set (> 600,00 rows). The
difference is just a second for queries returning 5000 rows.
"Gert-Jan Strik" wrote:

> It could. It depends on your query. If your query returns a large
> dataset, or if your query isn't actually a query, but a batch with a
> loop or cursor. In that case, make sure you SET NOCOUNT ON at the start
> of your batch.
> Gert-Jan
>
> sharman wrote:
>|||I've seen cases where AV programs will AV the named pipes used between the s
erver and the client.
So, if the client connects using Named Pipes, you want to check this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:68E0A5EA-9B90-4A24-A0BF-754E5A96B530@.microsoft.com...[vbcol=seagreen]
> You are right, it is returning a large data set (> 600,00 rows). The
> difference is just a second for queries returning 5000 rows.
> "Gert-Jan Strik" wrote:
>

Performance

Hi,
Our production server is running very slow. A query on Staging server takes
less than 1 minute is taking more than 8 minutes on Production. It's the same
with all the databases on Production server. I have looked and found that
there is no blocking. What's the best way to troubleshoot ? Thanks.Start with comparing the execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> Hi,
> Our production server is running very slow. A query on Staging server takes
> less than 1 minute is taking more than 8 minutes on Production. It's the same
> with all the databases on Production server. I have looked and found that
> there is no blocking. What's the best way to troubleshoot ? Thanks.|||The best way is to get an expert in to give you a quick performance review,
and then mentor you on how to be better at it yourself. You could waste
days or weeks checking this and that as recommended by forum posters but a
consultant could nail it down in a few hours or even minutes.
--
TheSQLGuru
President
Indicium Resources, Inc.
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> Hi,
> Our production server is running very slow. A query on Staging server
> takes
> less than 1 minute is taking more than 8 minutes on Production. It's the
> same
> with all the databases on Production server. I have looked and found that
> there is no blocking. What's the best way to troubleshoot ? Thanks.|||I did just that and the execution plans are exactly same. The jobs for
optimizing and defargging the indexes are fine and running as scheduled. The
only difference between the two servers is that the Staging is on the Local
Network and the Production is at a different location. Could it be the
network pipe causing the delay?
"Tibor Karaszi" wrote:
> Start with comparing the execution plans.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> > Hi,
> >
> > Our production server is running very slow. A query on Staging server takes
> > less than 1 minute is taking more than 8 minutes on Production. It's the same
> > with all the databases on Production server. I have looked and found that
> > there is no blocking. What's the best way to troubleshoot ? Thanks.
>|||It could. It depends on your query. If your query returns a large
dataset, or if your query isn't actually a query, but a batch with a
loop or cursor. In that case, make sure you SET NOCOUNT ON at the start
of your batch.
Gert-Jan
sharman wrote:
> I did just that and the execution plans are exactly same. The jobs for
> optimizing and defargging the indexes are fine and running as scheduled. The
> only difference between the two servers is that the Staging is on the Local
> Network and the Production is at a different location. Could it be the
> network pipe causing the delay?
> "Tibor Karaszi" wrote:
> > Start with comparing the execution plans.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> > > Hi,
> > >
> > > Our production server is running very slow. A query on Staging server takes
> > > less than 1 minute is taking more than 8 minutes on Production. It's the same
> > > with all the databases on Production server. I have looked and found that
> > > there is no blocking. What's the best way to troubleshoot ? Thanks.
> >
> >|||You are right, it is returning a large data set (> 600,00 rows). The
difference is just a second for queries returning 5000 rows.
"Gert-Jan Strik" wrote:
> It could. It depends on your query. If your query returns a large
> dataset, or if your query isn't actually a query, but a batch with a
> loop or cursor. In that case, make sure you SET NOCOUNT ON at the start
> of your batch.
> Gert-Jan
>
> sharman wrote:
> >
> > I did just that and the execution plans are exactly same. The jobs for
> > optimizing and defargging the indexes are fine and running as scheduled. The
> > only difference between the two servers is that the Staging is on the Local
> > Network and the Production is at a different location. Could it be the
> > network pipe causing the delay?
> >
> > "Tibor Karaszi" wrote:
> >
> > > Start with comparing the execution plans.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://sqlblog.com/blogs/tibor_karaszi
> > >
> > >
> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > > news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
> > > > Hi,
> > > >
> > > > Our production server is running very slow. A query on Staging server takes
> > > > less than 1 minute is taking more than 8 minutes on Production. It's the same
> > > > with all the databases on Production server. I have looked and found that
> > > > there is no blocking. What's the best way to troubleshoot ? Thanks.
> > >
> > >
>|||I've seen cases where AV programs will AV the named pipes used between the server and the client.
So, if the client connects using Named Pipes, you want to check this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:68E0A5EA-9B90-4A24-A0BF-754E5A96B530@.microsoft.com...
> You are right, it is returning a large data set (> 600,00 rows). The
> difference is just a second for queries returning 5000 rows.
> "Gert-Jan Strik" wrote:
>> It could. It depends on your query. If your query returns a large
>> dataset, or if your query isn't actually a query, but a batch with a
>> loop or cursor. In that case, make sure you SET NOCOUNT ON at the start
>> of your batch.
>> Gert-Jan
>>
>> sharman wrote:
>> >
>> > I did just that and the execution plans are exactly same. The jobs for
>> > optimizing and defargging the indexes are fine and running as scheduled. The
>> > only difference between the two servers is that the Staging is on the Local
>> > Network and the Production is at a different location. Could it be the
>> > network pipe causing the delay?
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > Start with comparing the execution plans.
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://sqlblog.com/blogs/tibor_karaszi
>> > >
>> > >
>> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > > news:5103C554-9852-4712-A4CA-A9C305999ADA@.microsoft.com...
>> > > > Hi,
>> > > >
>> > > > Our production server is running very slow. A query on Staging server takes
>> > > > less than 1 minute is taking more than 8 minutes on Production. It's the same
>> > > > with all the databases on Production server. I have looked and found that
>> > > > there is no blocking. What's the best way to troubleshoot ? Thanks.
>> > >
>> > >

Perfomance monitor

Hi all,
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomance
a lot? If there a better way for me to do the samilar thing?
Thanks.See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.com/performance_monitor_tips.asp
AMB
"Catelin Wang" wrote:
> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfomance
> a lot? If there a better way for me to do the samilar thing?
> Thanks.|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.com/performance_monitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:
> > Hi all,
> > I am thinking how bad it can be if I have perfomance monitor and sql
> > profiler running for my production SQL server? do they affact the perfomance
> > a lot? If there a better way for me to do the samilar thing?
> >
> > Thanks.

Perfomance monitor

Hi all,
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomanc
e
a lot? If there a better way for me to do the samilar thing?
Thanks.See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_sid..._sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.c...ofiler_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.c...onitor_tips.asp
AMB
"Catelin Wang" wrote:

> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfoma
nce
> a lot? If there a better way for me to do the samilar thing?
> Thanks.|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.c...ofiler_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.c...onitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:
>

Perfomance monitor

Hi all,
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomance
a lot? If there a better way for me to do the samilar thing?
Thanks.
See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_side...sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.co...filer_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.co...nitor_tips.asp
AMB
"Catelin Wang" wrote:

> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfomance
> a lot? If there a better way for me to do the samilar thing?
> Thanks.
|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_side...sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.co...filer_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.co...nitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:

Perfomance

I have stored procedure that is taking longer time in production than in
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
Rahul
Plenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>

Perfomance

I have stored procedure that is taking longer time in production than in
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
RahulPlenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>

Perfomance

I have stored procedure that is taking longer time in production than in
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
RahulPlenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>