Prev: Trouble Installing Cumulative Update 2 to SQL Server 2008 SP1, Hotfix KB958186
Next: Windows/SQL Authentication...
From: TheSQLGuru on 10 Nov 2009 11:28
Again I will say you must examine the actual running query plan.
Tune/refactor if necessary. Also check for file and wait stats to see where
the actual bottleneck is. Virtualized system just adds in more options for
being suboptimally configured. :(

Your focus on plan caches is, I believe (with limited information here)
misguided and not helping you solve the performance issue.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Zaur Bahramov" <zbakhramov(a)msn.com> wrote in message
news:ugGWsGTYKHA.4816(a)TK2MSFTNGP06.phx.gbl...
> Zaur Bahramov wrote:
>> TheSQLGuru wrote:
>>> Nothing you posted is useful unfortunately. you need to see the actual
>>> query plan that is executing. If you are VERY lucky, you will have some
>>> form of cursor in there (which I highly suspect given that 'best case'
>>> runtimes are in the many hour range) AND that there are one or more
>>> indexes missing you can apply 'between' cursor transaction batches.
>>> Otherwise you are screwed at this point because if a lot of work has
>>> been done it would potentially have to be 'unwound' if you cancel the
>>> operation which could take as long as it has been running to this point.
>>> tlog size/percent full can be evaluated as an indicator here.
>>>
>>> you can investigate current activity using a number of DMVs and/or
>>> profiler. query plan information is available as well.
>>>
>>> Personally I recommend you get a tuning consultant to hook up to your
>>> system to see if they can help.
>>>
>>
>> Our systsem is Windows Server 2008 R2 x64 which in fact is a VM on ESX
>> 3.5 . We are going to do an upgrade to vSphere soon, since ESX 3.5 has
>> only an experimental support for Windows Server 2008.
>> As for the database it's about 150 Gb, log file is on a separate disk.
>> RAM 8 Gb - I've configured Minimum memory on SQL as 0 and max as 6656.
>> This was I give 1.5Gb to OS and the rest 6.5 Gb to SQL.
>> After restoring an fbk to sql 2008 I created several maintenance tasks as
>> follows and ran them:
>> 1) Update statistics
>> 2) Rebuild and reorganize indexes
>> 3) Integrity check
>>
>> Compatibility level set to 100.
>>
>> In general that's all. Also, in the very beginning I've configured the
>> paging file as about 12Gb both min and max size, but then noticed in the
>> Task Manager -> Performance that the Memory column was all green (7.6Gb),
>> i.e. using all RAM for some reason, even when server was idle. I've set
>> the paging file to be managed by windows, and ot it's between 2.6-3.2Gb
>> even when procs are running.
>>
>> I've asked my chief to re-launch his procedure, that was usually running
>> 18 hrs on old server, and that didn't complete even after 4 days on a new
>> server, and will see what happens.
>>
>> Can you advise smth else on server configuration? What counters should I
>> use in these scenario, which DMVs to run and which values to
>> read/compare?
>>
>
> Also, it's very strange, how comes that DBCC PROCCACHE on old Windows 2000
> server gives:
> num proc buffs 38717
> num proc buffs used 38717
> num proc buffs active 13287
> proc cache size 32866
> proc cache used 32866
> proc cache active 7436
>
> while on a new server:
> num proc buffs 6358
> num proc buffs used 208
> num proc buffs active 208
> proc cache size 400
> proc cache used 13
> proc cache active 13
>
> I mean for example, why on the old server 'num proc buffs' is equal to
> 'num proc buffs used' while on a new server there's such a great
> difference?
>
> Which other DMVs can I use to troubleshoot the issue?


From: Zaur Bahramov on 11 Nov 2009 16:02
TheSQLGuru wrote:
> Again I will say you must examine the actual running query plan.
> Tune/refactor if necessary. Also check for file and wait stats to see where
> the actual bottleneck is. Virtualized system just adds in more options for
> being suboptimally configured. :(
>
> Your focus on plan caches is, I believe (with limited information here)
> misguided and not helping you solve the performance issue.
>

Looks like it IS a stored procedure. Namely, sp_execute and
sp_cursorexecute taking long time to run. I have checked in the trace
log. Both SPs have very high CPU, Reads, Writes and Duration.

TextData CPU Reads Writes Duration
exec sp_execute 368,1,3,5,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2009-09-30 00:00:00' 0 7 0 276
declare @p2 int set @p2=180157011 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
1073742190,@p2 output,@p3 output,@p4 output,@p5
output,1,3,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30
00:00:00' select @p2, @p3, @p4, @p5 32 1056 0 30383
exec sp_execute 85,1,3,5,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2009-09-30 00:00:00' 0 13 0 419
exec sp_execute 369,1,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2009-09-30 00:00:00' 0 4 0 112
declare @p2 int set @p2=180157013 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
1073742191,@p2 output,@p3 output,@p4 output,@p5
output,1,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30
00:00:00' select @p2, @p3, @p4, @p5 0 7 0 175
exec sp_cursorclose 180157013 0 0 0 58
exec sp_execute 369,5,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2009-09-30 00:00:00' 0 5 0 133
declare @p2 int set @p2=180157015 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
1073742191,@p2 output,@p3 output,@p4 output,@p5
output,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30
00:00:00' select @p2, @p3, @p4, @p5 0 7 0 140
exec sp_cursorclose 180157015 0 0 0 32
exec sp_execute 369,3,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2009-09-30 00:00:00' 0 4 0 127
declare @p2 int set @p2=180157017 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
1073742191,@p2 output,@p3 output,@p4 output,@p5
output,3,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30
00:00:00' select @p2, @p3, @p4, @p5 0 7 0 120
exec sp_cursorclose 180157003 0 0 0 24
exec sp_execute 369,4,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2009-09-30 00:00:00' 0 4 0 273
declare @p2 int set @p2=180157019 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
1073742182,@p2 output,@p3 output,@p4 output,@p5
output,4,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30
00:00:00' select @p2, @p3, @p4, @p5 0 7 0 186
exec sp_execute 86,4,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2009-09-30 00:00:00' 0 5 0 117
exec sp_execute 362,'0621178','2008-10-01 00:00:00','2009-09-30
00:00:00','FACTORY_NAME' 0 4 0 94
declare @p2 int set @p2=180157021 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=5 exec sp_cursorexecute
1073742183,@p2 output,@p3 output,@p4 output,@p5
output,'0621178','2008-10-01 00:00:00','2009-09-30
00:00:00','FACTORY_NAME' select @p2, @p3, @p4, @p5 0 29 0 279
exec sp_cursorfetch 180157021,2,0,20 0 105 0 845
exec sp_cursorfetch 180157021,2,0,56 0 165 0 834
exec sp_cursorclose 180157021 0 1 0 128
exec sp_cursorclose 180157011 0 0 0 37
exec sp_execute 368,0,2,6,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2008-12-31 00:00:00' 0 25 0 347
exec sp_cursorclose 180157009 0 0 0 23
exec sp_execute 361,0,6,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2008-12-31 00:00:00' 0 25 0 217
exec sp_execute 368,1,3,5,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2008-12-31 00:00:00' 0 7 0 103
declare @p2 int set @p2=180157023 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
1073742190,@p2 output,@p3 output,@p4 output,@p5
output,1,3,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31
00:00:00' select @p2, @p3, @p4, @p5 62 2288 0 55918
exec sp_execute 85,1,3,5,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2008-12-31 00:00:00' 0 13 0 292
exec sp_cursorclose 180157017 0 0 0 82
exec sp_execute 369,1,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2008-12-31 00:00:00' 0 4 0 103
exec sp_execute 369,5,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2008-12-31 00:00:00' 0 5 0 279
declare @p2 int set @p2=180157025 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
1073742191,@p2 output,@p3 output,@p4 output,@p5
output,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31
00:00:00' select @p2, @p3, @p4, @p5 0 7 0 215
exec sp_cursorclose 180157025 0 0 0 28
exec sp_execute 369,3,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2008-12-31 00:00:00' 0 4 0 127
declare @p2 int set @p2=180157027 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
1073742191,@p2 output,@p3 output,@p4 output,@p5
output,3,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31
00:00:00' select @p2, @p3, @p4, @p5 0 7 0 106
exec sp_cursorclose 180157019 0 0 0 21
exec sp_execute 369,4,'0621178','FACTORY_NAME','2008-10-01
00:00:00','2008-12-31 00:00:00' 0 4 0 87
exec sp_execute 362,'0621178','2008-10-01 00:00:00','2008-12-31
00:00:00','FACTORY_NAME' 0 4 0 87
declare @p2 int set @p2=180157029 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=5 exec sp_cursorexecute
1073742183,@p2 output,@p3 output,@p4 output,@p5
output,'0621178','2008-10-01 00:00:00','2008-12-31
00:00:00','FACTORY_NAME' select @p2, @p3, @p4, @p5 0 29 0 279
exec sp_cursorfetch 180157029,2,0,20 0 15 0 185
exec sp_cursorclose 180157029 0 1 0 63
exec sp_cursorclose 180157023 0 0 0 28
exec sp_execute 368,0,2,6,'0621178','FACTORY_NAME','2009-01-01
00:00:00','2009-03-31 00:00:00' 0 31 0 301
declare @p2 int set @p2=180157031 declare @p3 int set @p3=2 declare
@p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
1073742190,@p2 output,@p3 output,@p4 output,@p5
output,0,2,6,'0621178','FACTORY_NAME','2009-01-01 00:00:00','2009-03-31
00:00:00' select @p2, @p3, @p4, @p5 0 60 0 1494


I've also checked on SQL Server 2000 and these values are very low on
the production server.

I've checked some forums and noticed that some other people also have
had the similar problem with these SPs when migrating a DB from 2000 to
2005. In our case it's SQL Server 2008 though.
From: TheSQLGuru on 11 Nov 2009 21:43
So it is cursor based, as I initially suspected.

Now you mention SQL 2000. Did you upgrade this database from 2000 to 2008?
If so, did you run full statistics updates on everything with full scan?

What are the actual query plans of these cursor executions?

Did you verify no schema changes between your 2000 and 2008 systems?

LOTS more could be at issue here. I will reiterate that you should get a
professional tuner on board for a performance review.


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Zaur Bahramov" <zbakhramov(a)msn.com> wrote in message
news:eiyZYJxYKHA.2188(a)TK2MSFTNGP04.phx.gbl...
> TheSQLGuru wrote:
>> Again I will say you must examine the actual running query plan.
>> Tune/refactor if necessary. Also check for file and wait stats to see
>> where the actual bottleneck is. Virtualized system just adds in more
>> options for being suboptimally configured. :(
>>
>> Your focus on plan caches is, I believe (with limited information here)
>> misguided and not helping you solve the performance issue.
>>
>
> Looks like it IS a stored procedure. Namely, sp_execute and
> sp_cursorexecute taking long time to run. I have checked in the trace log.
> Both SPs have very high CPU, Reads, Writes and Duration.
>
> TextData CPU Reads Writes Duration
> exec sp_execute 368,1,3,5,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2009-09-30 00:00:00' 0 7 0 276
> declare @p2 int set @p2=180157011 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
> 1073742190,@p2 output,@p3 output,@p4 output,@p5
> output,1,3,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30
> 00:00:00' select @p2, @p3, @p4, @p5 32 1056 0 30383
> exec sp_execute 85,1,3,5,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2009-09-30 00:00:00' 0 13 0 419
> exec sp_execute 369,1,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2009-09-30 00:00:00' 0 4 0 112
> declare @p2 int set @p2=180157013 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
> 1073742191,@p2 output,@p3 output,@p4 output,@p5
> output,1,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30
> 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 175
> exec sp_cursorclose 180157013 0 0 0 58
> exec sp_execute 369,5,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2009-09-30 00:00:00' 0 5 0 133
> declare @p2 int set @p2=180157015 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
> 1073742191,@p2 output,@p3 output,@p4 output,@p5
> output,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30
> 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 140
> exec sp_cursorclose 180157015 0 0 0 32
> exec sp_execute 369,3,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2009-09-30 00:00:00' 0 4 0 127
> declare @p2 int set @p2=180157017 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
> 1073742191,@p2 output,@p3 output,@p4 output,@p5
> output,3,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30
> 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 120
> exec sp_cursorclose 180157003 0 0 0 24
> exec sp_execute 369,4,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2009-09-30 00:00:00' 0 4 0 273
> declare @p2 int set @p2=180157019 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
> 1073742182,@p2 output,@p3 output,@p4 output,@p5
> output,4,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30
> 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 186
> exec sp_execute 86,4,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2009-09-30 00:00:00' 0 5 0 117
> exec sp_execute 362,'0621178','2008-10-01 00:00:00','2009-09-30
> 00:00:00','FACTORY_NAME' 0 4 0 94
> declare @p2 int set @p2=180157021 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=5 exec sp_cursorexecute
> 1073742183,@p2 output,@p3 output,@p4 output,@p5
> output,'0621178','2008-10-01 00:00:00','2009-09-30
> 00:00:00','FACTORY_NAME' select @p2, @p3, @p4, @p5 0 29 0 279
> exec sp_cursorfetch 180157021,2,0,20 0 105 0 845
> exec sp_cursorfetch 180157021,2,0,56 0 165 0 834
> exec sp_cursorclose 180157021 0 1 0 128
> exec sp_cursorclose 180157011 0 0 0 37
> exec sp_execute 368,0,2,6,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2008-12-31 00:00:00' 0 25 0 347
> exec sp_cursorclose 180157009 0 0 0 23
> exec sp_execute 361,0,6,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2008-12-31 00:00:00' 0 25 0 217
> exec sp_execute 368,1,3,5,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2008-12-31 00:00:00' 0 7 0 103
> declare @p2 int set @p2=180157023 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
> 1073742190,@p2 output,@p3 output,@p4 output,@p5
> output,1,3,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31
> 00:00:00' select @p2, @p3, @p4, @p5 62 2288 0 55918
> exec sp_execute 85,1,3,5,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2008-12-31 00:00:00' 0 13 0 292
> exec sp_cursorclose 180157017 0 0 0 82
> exec sp_execute 369,1,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2008-12-31 00:00:00' 0 4 0 103
> exec sp_execute 369,5,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2008-12-31 00:00:00' 0 5 0 279
> declare @p2 int set @p2=180157025 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
> 1073742191,@p2 output,@p3 output,@p4 output,@p5
> output,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31
> 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 215
> exec sp_cursorclose 180157025 0 0 0 28
> exec sp_execute 369,3,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2008-12-31 00:00:00' 0 4 0 127
> declare @p2 int set @p2=180157027 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
> 1073742191,@p2 output,@p3 output,@p4 output,@p5
> output,3,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31
> 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 106
> exec sp_cursorclose 180157019 0 0 0 21
> exec sp_execute 369,4,'0621178','FACTORY_NAME','2008-10-01
> 00:00:00','2008-12-31 00:00:00' 0 4 0 87
> exec sp_execute 362,'0621178','2008-10-01 00:00:00','2008-12-31
> 00:00:00','FACTORY_NAME' 0 4 0 87
> declare @p2 int set @p2=180157029 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=5 exec sp_cursorexecute
> 1073742183,@p2 output,@p3 output,@p4 output,@p5
> output,'0621178','2008-10-01 00:00:00','2008-12-31
> 00:00:00','FACTORY_NAME' select @p2, @p3, @p4, @p5 0 29 0 279
> exec sp_cursorfetch 180157029,2,0,20 0 15 0 185
> exec sp_cursorclose 180157029 0 1 0 63
> exec sp_cursorclose 180157023 0 0 0 28
> exec sp_execute 368,0,2,6,'0621178','FACTORY_NAME','2009-01-01
> 00:00:00','2009-03-31 00:00:00' 0 31 0 301
> declare @p2 int set @p2=180157031 declare @p3 int set @p3=2 declare
> @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute
> 1073742190,@p2 output,@p3 output,@p4 output,@p5
> output,0,2,6,'0621178','FACTORY_NAME','2009-01-01 00:00:00','2009-03-31
> 00:00:00' select @p2, @p3, @p4, @p5 0 60 0 1494
>
>
> I've also checked on SQL Server 2000 and these values are very low on the
> production server.
>
> I've checked some forums and noticed that some other people also have had
> the similar problem with these SPs when migrating a DB from 2000 to 2005.
> In our case it's SQL Server 2008 though.


First  |  Prev  | 
Pages: 1 2
Prev: Trouble Installing Cumulative Update 2 to SQL Server 2008 SP1, Hotfix KB958186
Next: Windows/SQL Authentication...