Performance Help

I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
queries against it every night. These queries are for reporting purposes and
need to run within certain time constraints. I have added indexes to the
tables, but the queries could potentially include filter criteria for any
number of 100 or so columns. At this point, i am at a loss of how to optimize
a database of this size properly to faciliate these requirements. Any
thoughts?
Thanks,
Matt
--
Message posted via http://www.sqlmonster.comOne easy solution could be to use Database Engine Tuning Advisor (DTA).
Create a trace using Profiler or (better) a server trace to collect all these
queries and use this trace file as input to DTA. Then follow the index and
statistics recommendations from DTA.
A better solution would be to use a tuning methodology like the one
described by Itzik Ben-Gan on his book Inside SQL Server 2005: T-SQL Querying
(Chapter 3, Tuning Methodology).
Hope this helps,
Ben Nevarez
"lotek via SQLMonster.com" wrote:
> I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
> queries against it every night. These queries are for reporting purposes and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.sqlmonster.com
>|||Some rather high-level and general suggestions:
1. Use SQL Trace to record these queries, and use the recorded queries as
your base workload for performance analysis.
2. Run the workload through the database engine tuning advisor and analyze
the index recommendations. Note that the recommendations from the database
tuning advisor may not always be spot on, but you can take them as input to
your analysis.
3. Concentrate on the top N most expensive queries and focus your tuning
efforts them first.
Linchi
"lotek via SQLMonster.com" wrote:
> I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
> queries against it every night. These queries are for reporting purposes and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.sqlmonster.com
>|||Make sure that the queries are executed at a time when the server is not
busy doing anything else, when it is experiencing the least amount of
activity. You may also want to check what else runs on the server outside
of mssql and terminate as many unnecessary running processes as possible.
--
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:83BE6CA5-2EA6-4177-8559-07675C44F8DE@.microsoft.com...
> Some rather high-level and general suggestions:
> 1. Use SQL Trace to record these queries, and use the recorded queries as
> your base workload for performance analysis.
> 2. Run the workload through the database engine tuning advisor and analyze
> the index recommendations. Note that the recommendations from the database
> tuning advisor may not always be spot on, but you can take them as input
> to
> your analysis.
> 3. Concentrate on the top N most expensive queries and focus your tuning
> efforts them first.
> Linchi
> "lotek via SQLMonster.com" wrote:
>> I have a 40 gig database (SQL 2005 Standard) and need to run several
>> hundred
>> queries against it every night. These queries are for reporting purposes
>> and
>> need to run within certain time constraints. I have added indexes to the
>> tables, but the queries could potentially include filter criteria for any
>> number of 100 or so columns. At this point, i am at a loss of how to
>> optimize
>> a database of this size properly to faciliate these requirements. Any
>> thoughts?
>> Thanks,
>> Matt
>> --
>> Message posted via http://www.sqlmonster.com
>>|||also, see if you can partition any of your data and tables and run your
queries in parallel
--
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"lotek via SQLMonster.com" <u16539@.uwe> wrote in message
news:7fc7a9d0d08f9@.uwe...
>I have a 40 gig database (SQL 2005 Standard) and need to run several
>hundred
> queries against it every night. These queries are for reporting purposes
> and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to
> optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.sqlmonster.com
>|||1) Get professional assistance
2) Buy better hardware (especially RAM, more CPUs and FAST I/O)
3) Investigate partitioning
4) If you use lots of char-type fields, investigate Full Text Indexing
5) If your queries access large portions of the data (10-20% plus) indexing
(full or regular) won't help because the optimizer will choose table scans
(unless you have covering indexes).
6) Check for locking. Consider using NOLOCK hints on the report queries.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"lotek via SQLMonster.com" <u16539@.uwe> wrote in message
news:7fc7a9d0d08f9@.uwe...
>I have a 40 gig database (SQL 2005 Standard) and need to run several
>hundred
> queries against it every night. These queries are for reporting purposes
> and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to
> optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.sqlmonster.com
>sql

Performance Help

I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
queries against it every night. These queries are for reporting purposes and
need to run within certain time constraints. I have added indexes to the
tables, but the queries could potentially include filter criteria for any
number of 100 or so columns. At this point, i am at a loss of how to optimize
a database of this size properly to faciliate these requirements. Any
thoughts?
Thanks,
Matt
Message posted via http://www.droptable.com
One easy solution could be to use Database Engine Tuning Advisor (DTA).
Create a trace using Profiler or (better) a server trace to collect all these
queries and use this trace file as input to DTA. Then follow the index and
statistics recommendations from DTA.
A better solution would be to use a tuning methodology like the one
described by Itzik Ben-Gan on his book Inside SQL Server 2005: T-SQL Querying
(Chapter 3, Tuning Methodology).
Hope this helps,
Ben Nevarez
"lotek via droptable.com" wrote:

> I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
> queries against it every night. These queries are for reporting purposes and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.droptable.com
>
|||Some rather high-level and general suggestions:
1. Use SQL Trace to record these queries, and use the recorded queries as
your base workload for performance analysis.
2. Run the workload through the database engine tuning advisor and analyze
the index recommendations. Note that the recommendations from the database
tuning advisor may not always be spot on, but you can take them as input to
your analysis.
3. Concentrate on the top N most expensive queries and focus your tuning
efforts them first.
Linchi
"lotek via droptable.com" wrote:

> I have a 40 gig database (SQL 2005 Standard) and need to run several hundred
> queries against it every night. These queries are for reporting purposes and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.droptable.com
>
|||Make sure that the queries are executed at a time when the server is not
busy doing anything else, when it is experiencing the least amount of
activity. You may also want to check what else runs on the server outside
of mssql and terminate as many unnecessary running processes as possible.
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:83BE6CA5-2EA6-4177-8559-07675C44F8DE@.microsoft.com...[vbcol=seagreen]
> Some rather high-level and general suggestions:
> 1. Use SQL Trace to record these queries, and use the recorded queries as
> your base workload for performance analysis.
> 2. Run the workload through the database engine tuning advisor and analyze
> the index recommendations. Note that the recommendations from the database
> tuning advisor may not always be spot on, but you can take them as input
> to
> your analysis.
> 3. Concentrate on the top N most expensive queries and focus your tuning
> efforts them first.
> Linchi
> "lotek via droptable.com" wrote:
|||also, see if you can partition any of your data and tables and run your
queries in parallel
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"lotek via droptable.com" <u16539@.uwe> wrote in message
news:7fc7a9d0d08f9@.uwe...
>I have a 40 gig database (SQL 2005 Standard) and need to run several
>hundred
> queries against it every night. These queries are for reporting purposes
> and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to
> optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.droptable.com
>
|||1) Get professional assistance
2) Buy better hardware (especially RAM, more CPUs and FAST I/O)
3) Investigate partitioning
4) If you use lots of char-type fields, investigate Full Text Indexing
5) If your queries access large portions of the data (10-20% plus) indexing
(full or regular) won't help because the optimizer will choose table scans
(unless you have covering indexes).
6) Check for locking. Consider using NOLOCK hints on the report queries.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"lotek via droptable.com" <u16539@.uwe> wrote in message
news:7fc7a9d0d08f9@.uwe...
>I have a 40 gig database (SQL 2005 Standard) and need to run several
>hundred
> queries against it every night. These queries are for reporting purposes
> and
> need to run within certain time constraints. I have added indexes to the
> tables, but the queries could potentially include filter criteria for any
> number of 100 or so columns. At this point, i am at a loss of how to
> optimize
> a database of this size properly to faciliate these requirements. Any
> thoughts?
> Thanks,
> Matt
> --
> Message posted via http://www.droptable.com
>

Performance difference between query executed through ASP.NET and SSMS

I have also posted this in microsoft.public.sqlserver.programming.

I have a query which, depending on where I run it from, will either take 10 milliseconds or 10 seconds.

The query works perfectly when run in SQL Server Management Studio... in my database of around 70,000 items it returns the results in around 10ms. It uses all my indexes and indexed views correctly.

However when I run the identical query from my ASP.NET application, it takes around 10 seconds... 1000 times longer.

Looking at it in Sql Server Profiler I can't see any difference in the query, except from ASP.NET it needs 62531 reads and from SSMS it needs only 318 reads. If I copy the slow running ASP.NET query from the profiler into SSMS, then it runs quick again. The results returned are the same.

I have provided more details of the query below, but I guess my real question is: What is the best way to debug this? I'm not an expert with SQL Server, so any pointers on where I should start looking to find the difference in how the query is being executed would be a great help.

The query is of the form:

WITH RowPost AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DateCreated DESC) AS Row,
ItemId,
Title,
....
FROM
Items_View WITH(NOEXPAND)
WHERE ItemX >= @.minX AND ItemX <= @.maxX AND ItemY >= @.minY AND ItemY <= @.maxY
)
SELECT
*,
(SELECT Count(*) FROM RowPost) AS [Count]
FROM RowPost
WHERE Row >= @.minRow AND Row < @.maxRow

Where Items_View is an indexed view, and WITH(NOEXPAND) is being used to force it to use the indexed view (this is optimal). The line beginning "SELECT Count(*)" is to get the total number of results (without having to run the inner query a second time).

This is running against SQL Server Developer Edition.

My guess is its using a different query plan. Query plan reuse is influenced by your connection settings eg SET ANSI NULLS, QUOTED IDENTIFIER etc... and these are most likely different between your aspnet conn and ssms.

Check the setopts values in sys.syscacheobjects and for your queries and see if they differ. You can translate the values by using the following code:

Code Snippet

DECLARE @.sets int
SELECT @.sets = 4347

IF @.sets & 1 = 1 PRINT 'ANSI_PADDING'
IF @.sets & 4 = 4 PRINT 'FORCEPLAN'
IF @.sets & 8 = 8 PRINT 'CONCAT_NULL_YIELDS_NULL'
IF @.sets & 16 = 16 PRINT 'ANSI_WARNINGS'
IF @.sets & 32 = 32 PRINT 'ANSI_NULLS'
IF @.sets & 64 = 64 PRINT 'QUOTED_IDENTIFIER'
IF @.sets & 128 = 128 PRINT 'ANSI_NULL_DFLT_ON'
IF @.sets & 256 = 256 PRINT 'ANSI_NULL_DFLT_OFF'
IF @.sets & 4096 = 4096 PRINT 'ARITHABORT'

HTH!|||

With the information from everyone (both in the MS newsgroup and in the MSDN forums - thanks!) I started looking into this by checking the 'setops' in the sys.syscacheobjects view for the query when executed by ASP.NET and SSMS.

While doing this, I also ran "DBCC DROPCLEANBUFFERS" and "DBCC FREEPROCCACHE".

The first thing I noticed was, as Erland predicted (although I hadn't seen his post at this point), the main difference was ARITHABORT was on for SSMS and off for ASP.NET.

This worried me - if the query was only fast because it was aborting early then I'd have a big problem.

I started to play with this setting to see what the difference was... but to my surprise not only did it not make a difference but my ASP.NET queries were suddenly fast again.

I now can't reproduce the ASP.NET queries being slow. Perhaps clearing the data cache and execution plans forced SQL Server to build a new execution plan and this time it chose a fast one?

Does this sound plausable? Is it a problem people have experienced before, and is there a way to ensure fast execution plans are always used?

Cheers,
James.

|||

For anyone interested, this problem re-occurred after a server restart, but this time (thanks to all the replies here and on the newsgroup) I had the knowlege to debug it.

After turning on Showplan XML in SQL Server Profiler I saw that the fast version of the query was using an index on the indexed view that I had created specifically for the query. The slower query had decided to use a different index that I had created for a slightly different query and wasn't sorted as effectively, which meant it was having to do a manual sort afterwards.

I added a hint to use the correct index and now it is running full speed again.

I believe this occured because sometimes the first query after the server restart or cache clear would be one where the sorting was trivial due to the small number of results returned. It would therefore create a sub-optimal execution plan for the majority of queries where there are many more results returned.

Thanks,
James.

|||Excellent stuff, James. Glad you got it sorted.

performance degradation during backup

Whenever I run database backup during normal operating
hours I see a performance degradation and somtimes
blocking. We are running on active/passive config on MS
Cluster. The database size is 40GB and the backup takes
about an hour to complete. I have it set to run at
midnight but there are cases where I need to run the
backup during normal hrs. I have no experience in MS
Cluster and my gut feeling is that the I/O rate max data
thruput is getting maxed out when I run the backup during
normal operating hrs. I can not let the backup run and
run the perfmon to figure out what is going on because
the end users are annoyed and calling my COO ...
Our cluster does a 221GB backup in about 5hr, so your backup performance
doesn't seem unusual. If the users are seeing performance hits, I'm not
sure it is due to the backups - unless you are backing up to the same disk
as where the data files live.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"L Gonzales" <anonymous@.discussions.microsoft.com> wrote in message
news:23fe01c4dd5c$3de60130$a301280a@.phx.gbl...
Whenever I run database backup during normal operating
hours I see a performance degradation and somtimes
blocking. We are running on active/passive config on MS
Cluster. The database size is 40GB and the backup takes
about an hour to complete. I have it set to run at
midnight but there are cases where I need to run the
backup during normal hrs. I have no experience in MS
Cluster and my gut feeling is that the I/O rate max data
thruput is getting maxed out when I run the backup during
normal operating hrs. I can not let the backup run and
run the perfmon to figure out what is going on because
the end users are annoyed and calling my COO ...
|||L Gonzales wrote:
> Whenever I run database backup during normal operating
> hours I see a performance degradation and somtimes
> blocking. We are running on active/passive config on MS
> Cluster. The database size is 40GB and the backup takes
> about an hour to complete. I have it set to run at
> midnight but there are cases where I need to run the
> backup during normal hrs. I have no experience in MS
> Cluster and my gut feeling is that the I/O rate max data
> thruput is getting maxed out when I run the backup during
> normal operating hrs. I can not let the backup run and
> run the perfmon to figure out what is going on because
> the end users are annoyed and calling my COO ...
Are you backing up to disk on a different channel? If not, you should
back up to a mirrored array, not a RAID 5 array. You would also benefit
from any of the SQL Server backup programs out there that compress
backups as they can backup much, much faster than native (not to mention
restores are also faster).
David Gugick
Imceda Software
www.imceda.com
|||Hi
What you are seeing is a Disk performance issue. Whaqt is your disk
configuration?
Is your SAN setup with RAID-5 and the Data and dumps are on the same LUN/set
of disks? Reading and writing to and from the same LUN will really hamper
performance. What does performance monitor say about Disk Queue Length?
We dump at 2.5Tb per hour on our Clustered SQL Servers with SANs.
Regards
Mike
"L Gonzales" wrote:

> Whenever I run database backup during normal operating
> hours I see a performance degradation and somtimes
> blocking. We are running on active/passive config on MS
> Cluster. The database size is 40GB and the backup takes
> about an hour to complete. I have it set to run at
> midnight but there are cases where I need to run the
> backup during normal hrs. I have no experience in MS
> Cluster and my gut feeling is that the I/O rate max data
> thruput is getting maxed out when I run the backup during
> normal operating hrs. I can not let the backup run and
> run the perfmon to figure out what is going on because
> the end users are annoyed and calling my COO ...
>
>
|||Raid-10. Same LUN/set of disks.
>--Original Message--
>Hi
>What you are seeing is a Disk performance issue. Whaqt
is your disk
>configuration?
>Is your SAN setup with RAID-5 and the Data and dumps are
on the same LUN/set
>of disks? Reading and writing to and from the same LUN
will really hamper
>performance. What does performance monitor say about
Disk Queue Length?
>We dump at 2.5Tb per hour on our Clustered SQL Servers
with SANs.[vbcol=seagreen]
>Regards
>Mike
>"L Gonzales" wrote:
MS[vbcol=seagreen]
takes[vbcol=seagreen]
data[vbcol=seagreen]
during[vbcol=seagreen]
and[vbcol=seagreen]
because
>.
>
|||<anonymous@.discussions.microsoft.com> wrote in message
news:247101c4dd6c$60b0ea30$a301280a@.phx.gbl...
> Raid-10. Same LUN/set of disks.
Backup to a different LUN/set of disks.
|||Not only is that a poor choice for performance since you will most likely
max out the bandwidth but you are risking your database as well. If the
disk array died you would loose the database and your most recent backups.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:247101c4dd6c$60b0ea30$a301280a@.phx.gbl...[vbcol=seagreen]
> Raid-10. Same LUN/set of disks.
> is your disk
> on the same LUN/set
> will really hamper
> Disk Queue Length?
> with SANs.
> MS
> takes
> data
> during
> and
> because

performance degradation during backup

Whenever I run database backup during normal operating
hours I see a performance degradation and somtimes
blocking. We are running on active/passive config on MS
Cluster. The database size is 40GB and the backup takes
about an hour to complete. I have it set to run at
midnight but there are cases where I need to run the
backup during normal hrs. I have no experience in MS
Cluster and my gut feeling is that the I/O rate max data
thruput is getting maxed out when I run the backup during
normal operating hrs. I can not let the backup run and
run the perfmon to figure out what is going on because
the end users are annoyed and calling my COO ...Our cluster does a 221GB backup in about 5hr, so your backup performance
doesn't seem unusual. If the users are seeing performance hits, I'm not
sure it is due to the backups - unless you are backing up to the same disk
as where the data files live.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"L Gonzales" <anonymous@.discussions.microsoft.com> wrote in message
news:23fe01c4dd5c$3de60130$a301280a@.phx.gbl...
Whenever I run database backup during normal operating
hours I see a performance degradation and somtimes
blocking. We are running on active/passive config on MS
Cluster. The database size is 40GB and the backup takes
about an hour to complete. I have it set to run at
midnight but there are cases where I need to run the
backup during normal hrs. I have no experience in MS
Cluster and my gut feeling is that the I/O rate max data
thruput is getting maxed out when I run the backup during
normal operating hrs. I can not let the backup run and
run the perfmon to figure out what is going on because
the end users are annoyed and calling my COO ...|||L Gonzales wrote:
> Whenever I run database backup during normal operating
> hours I see a performance degradation and somtimes
> blocking. We are running on active/passive config on MS
> Cluster. The database size is 40GB and the backup takes
> about an hour to complete. I have it set to run at
> midnight but there are cases where I need to run the
> backup during normal hrs. I have no experience in MS
> Cluster and my gut feeling is that the I/O rate max data
> thruput is getting maxed out when I run the backup during
> normal operating hrs. I can not let the backup run and
> run the perfmon to figure out what is going on because
> the end users are annoyed and calling my COO ...
Are you backing up to disk on a different channel? If not, you should
back up to a mirrored array, not a RAID 5 array. You would also benefit
from any of the SQL Server backup programs out there that compress
backups as they can backup much, much faster than native (not to mention
restores are also faster).
--
David Gugick
Imceda Software
www.imceda.com|||Hi
What you are seeing is a Disk performance issue. Whaqt is your disk
configuration?
Is your SAN setup with RAID-5 and the Data and dumps are on the same LUN/set
of disks? Reading and writing to and from the same LUN will really hamper
performance. What does performance monitor say about Disk Queue Length?
We dump at 2.5Tb per hour on our Clustered SQL Servers with SANs.
Regards
Mike
"L Gonzales" wrote:
> Whenever I run database backup during normal operating
> hours I see a performance degradation and somtimes
> blocking. We are running on active/passive config on MS
> Cluster. The database size is 40GB and the backup takes
> about an hour to complete. I have it set to run at
> midnight but there are cases where I need to run the
> backup during normal hrs. I have no experience in MS
> Cluster and my gut feeling is that the I/O rate max data
> thruput is getting maxed out when I run the backup during
> normal operating hrs. I can not let the backup run and
> run the perfmon to figure out what is going on because
> the end users are annoyed and calling my COO ...
>
>|||Raid-10. Same LUN/set of disks.
>--Original Message--
>Hi
>What you are seeing is a Disk performance issue. Whaqt
is your disk
>configuration?
>Is your SAN setup with RAID-5 and the Data and dumps are
on the same LUN/set
>of disks? Reading and writing to and from the same LUN
will really hamper
>performance. What does performance monitor say about
Disk Queue Length?
>We dump at 2.5Tb per hour on our Clustered SQL Servers
with SANs.
>Regards
>Mike
>"L Gonzales" wrote:
>> Whenever I run database backup during normal operating
>> hours I see a performance degradation and somtimes
>> blocking. We are running on active/passive config on
MS
>> Cluster. The database size is 40GB and the backup
takes
>> about an hour to complete. I have it set to run at
>> midnight but there are cases where I need to run the
>> backup during normal hrs. I have no experience in MS
>> Cluster and my gut feeling is that the I/O rate max
data
>> thruput is getting maxed out when I run the backup
during
>> normal operating hrs. I can not let the backup run
and
>> run the perfmon to figure out what is going on
because
>> the end users are annoyed and calling my COO ...
>>
>.
>|||<anonymous@.discussions.microsoft.com> wrote in message
news:247101c4dd6c$60b0ea30$a301280a@.phx.gbl...
> Raid-10. Same LUN/set of disks.
Backup to a different LUN/set of disks.|||Not only is that a poor choice for performance since you will most likely
max out the bandwidth but you are risking your database as well. If the
disk array died you would loose the database and your most recent backups.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:247101c4dd6c$60b0ea30$a301280a@.phx.gbl...
> Raid-10. Same LUN/set of disks.
>>--Original Message--
>>Hi
>>What you are seeing is a Disk performance issue. Whaqt
> is your disk
>>configuration?
>>Is your SAN setup with RAID-5 and the Data and dumps are
> on the same LUN/set
>>of disks? Reading and writing to and from the same LUN
> will really hamper
>>performance. What does performance monitor say about
> Disk Queue Length?
>>We dump at 2.5Tb per hour on our Clustered SQL Servers
> with SANs.
>>Regards
>>Mike
>>"L Gonzales" wrote:
>> Whenever I run database backup during normal operating
>> hours I see a performance degradation and somtimes
>> blocking. We are running on active/passive config on
> MS
>> Cluster. The database size is 40GB and the backup
> takes
>> about an hour to complete. I have it set to run at
>> midnight but there are cases where I need to run the
>> backup during normal hrs. I have no experience in MS
>> Cluster and my gut feeling is that the I/O rate max
> data
>> thruput is getting maxed out when I run the backup
> during
>> normal operating hrs. I can not let the backup run
> and
>> run the perfmon to figure out what is going on
> because
>> the end users are annoyed and calling my COO ...
>>
>>.sql

Performance Dashboard Errors

After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :

The 'version_string' parameter is missing a value.

Anyone else seen this, and how did you fix the problem?

What is the service pack level on SQL Server?|||

I am getting the same error message

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

|||

How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.

-Sue

|||

I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)

I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.

The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.

Many thanks for any suggestions on this one.

John

|||

Any resolution on this?

Thanks

|||

Still nothing and even Google doesnt seem to have much of any use.

Its a real pity as I can see it would be useful to have the performance dashboard working.

|||

I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.

The performance_dashboard_main.rdl is a XML file so I searched for and found

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

which I changed to several different values (as a new filename) until I just removed it as below

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.

Performance Dashboard Errors

After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :

The 'version_string' parameter is missing a value.

Anyone else seen this, and how did you fix the problem?

What is the service pack level on SQL Server?|||

I am getting the same error message

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

|||

How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.

-Sue

|||

I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)

I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.

The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.

Many thanks for any suggestions on this one.

John

|||

Any resolution on this?

Thanks

|||

Still nothing and even Google doesnt seem to have much of any use.

Its a real pity as I can see it would be useful to have the performance dashboard working.

|||

I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.

The performance_dashboard_main.rdl is a XML file so I searched for and found

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

which I changed to several different values (as a new filename) until I just removed it as below

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.

Performance Dashboard Errors

After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :

The 'version_string' parameter is missing a value.

Anyone else seen this, and how did you fix the problem?

What is the service pack level on SQL Server?|||

I am getting the same error message

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

|||

How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.

-Sue

|||

I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)

I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.

The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.

Many thanks for any suggestions on this one.

John

|||

Any resolution on this?

Thanks

|||

Still nothing and even Google doesnt seem to have much of any use.

Its a real pity as I can see it would be useful to have the performance dashboard working.

|||

I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.

The performance_dashboard_main.rdl is a XML file so I searched for and found

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

which I changed to several different values (as a new filename) until I just removed it as below

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.

Performance Dashboard Errors

After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :

The 'version_string' parameter is missing a value.

Anyone else seen this, and how did you fix the problem?

What is the service pack level on SQL Server?|||

I am getting the same error message

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

|||

How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.

-Sue

|||

I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)

I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.

The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.

Many thanks for any suggestions on this one.

John

|||

Any resolution on this?

Thanks

|||

Still nothing and even Google doesnt seem to have much of any use.

Its a real pity as I can see it would be useful to have the performance dashboard working.

|||

I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.

The performance_dashboard_main.rdl is a XML file so I searched for and found

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

which I changed to several different values (as a new filename) until I just removed it as below

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.

Performance Dashboard Errors

After installing Performance Dashboard, I receive the following variations of the following error message when I run reports :

The 'version_string' parameter is missing a value.

Anyone else seen this, and how did you fix the problem?

What is the service pack level on SQL Server?|||

I am getting the same error message

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

|||

How are you trying to run the reports? You need to open performance_dashboard_main.rdl - that's the starting point. The reports are all subreports you access from the main dashboard report.

-Sue

|||

I am also getting the "error during processing of 'version_string' report" Error even though we have service pack 2 and the latest hotfix on our 32 bit clustered SQL 2005 server (current build 9.0.3054)

I can "remote desktop" to the server and run the "performance_dashboard_main.rdl" in a local SSMS "custom report" view but when I start SSMS on my local workstation with full admin rights on the server, opening "Custom reports" and pointing to a shared network directory OR my local "D:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard" directory containing all the RDL files and I get the above error.

The session I connect to SQL server with has full admin rights and I can do all my normal SSMS activities with no problems .. except run the performance dashboard.

Many thanks for any suggestions on this one.

John

|||

Any resolution on this?

Thanks

|||

Still nothing and even Google doesnt seem to have much of any use.

Its a real pity as I can see it would be useful to have the performance dashboard working.

|||

I have a "Fix" for this one though I dont yet know teh consequence of it fully. Since the "version_string" was coming back with an invalid value I decided to see if I could either out in a valid version string or get rid of it completely.

The performance_dashboard_main.rdl is a XML file so I searched for and found

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="1.0 (" + FormatDateTime(CDate("Jan 9, 2007")) + ")"</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

which I changed to several different values (as a new filename) until I just removed it as below

<ReportParameter Name="version_string">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=""</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>version_string</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

and the renamed rdl file "performance_dashboard_new_main.rdl" seems to work.

sql

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 Anomaly

I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 19
seconds. What can cause this severe difference in the performance of this
stored procedure?
When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?
|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:

> When you are running the procedure from your application, make sure there's
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could be
> that these options are different between Query Analyzer and your application
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>

Performance Anomaly

I am currently having a strange performance problem with one of my queries.
If I run a stored procedure from my web application or from Visual Studio it
will timeout after 5 minutes (my timeout setting). If run the stored
procedure using SQL Query Analyzer it executes without issue in 19 seconds.
What can cause this severe difference in the performance of this stored
procedure?Run a Profiler trace and see exactly how the procedure is being called
from your web application.|||Please do not multipost. I answered in .server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <TomMetrinex@.discussions.microsoft.com> wrote in message
news:5286E608-C003-4F01-B2AF-7913134C2996@.microsoft.com...
I am currently having a strange performance problem with one of my queries.
If I run a stored procedure from my web application or from Visual Studio it
will timeout after 5 minutes (my timeout setting). If run the stored
procedure using SQL Query Analyzer it executes without issue in 19 seconds.
What can cause this severe difference in the performance of this stored
procedure?

Performance Anomaly

I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 1
9
seconds. What can cause this severe difference in the performance of this
stored procedure?When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:

> When you are running the procedure from your application, make sure there'
s
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could
be
> that these options are different between Query Analyzer and your applicati
on
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in messa
ge
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>

Performance Anomaly

I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 19
seconds. What can cause this severe difference in the performance of this
stored procedure?When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:
> When you are running the procedure from your application, make sure there's
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could be
> that these options are different between Query Analyzer and your application
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>

Performance - Run Long Queries

A question about long running queries. We have an
application that is running across a WAN. We are trapping
long running queries. Does the timing of the query
include serving the data set back to the requestor?
L
Yes it does. That's why it is highly recommended that a stored procedure is
used because it is only running on the server and can take advantage of
cached plans.
"Laurel" <landj.griffith@.attbi.com> wrote in message
news:4e0801c48098$347dcae0$a301280a@.phx.gbl...
> A question about long running queries. We have an
> application that is running across a WAN. We are trapping
> long running queries. Does the timing of the query
> include serving the data set back to the requestor?
> L
>

Performance - Run Long Queries

A question about long running queries. We have an
application that is running across a WAN. We are trapping
long running queries. Does the timing of the query
include serving the data set back to the requestor?
LYes it does. That's why it is highly recommended that a stored procedure is
used because it is only running on the server and can take advantage of
cached plans.
"Laurel" <landj.griffith@.attbi.com> wrote in message
news:4e0801c48098$347dcae0$a301280a@.phx.gbl...
> A question about long running queries. We have an
> application that is running across a WAN. We are trapping
> long running queries. Does the timing of the query
> include serving the data set back to the requestor?
> L
>

Performance - Run Long Queries

A question about long running queries. We have an
application that is running across a WAN. We are trapping
long running queries. Does the timing of the query
include serving the data set back to the requestor?
LYes it does. That's why it is highly recommended that a stored procedure is
used because it is only running on the server and can take advantage of
cached plans.
"Laurel" <landj.griffith@.attbi.com> wrote in message
news:4e0801c48098$347dcae0$a301280a@.phx.gbl...
> A question about long running queries. We have an
> application that is running across a WAN. We are trapping
> long running queries. Does the timing of the query
> include serving the data set back to the requestor?
> L
>