| Author |
 Topic  |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
 Posted - 11/26/2010 : 11:26:08
|
"wouldn't it be easier to just replace the temp table with table variable? this way you don't need to change parent sprocs."
A @TableVar in Parent Sproc is visible to Child Sproc in SQL2008?
Have put the modified Sproc live but it is still recompiling/. I suspect that is the influence of the @MyCatalogue parameter (which I didn't test on Staging Doh!)
"For the longer term you might want to look at some sort of caching on the web side (assuming you don't do it already) e.g. Squid or Nginx."
Dunno about those, but all our pages are dynamic - built for CMS snippets. We have caching of various things - so, for example, a Menu is constructed from the Child Pages in the Page Tree, and this block of HTML is cached. If the page tree changes (Page added / Dropped) then the cache entry will be regenerated.
So, in the main, the various Panes on the page are cached - and not constructed each page-view. But the Order Summary and stuff like that is generated dynamically for each page view. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/26/2010 : 12:08:05
|
Have you looked in detail at SQL Server PerfMon statistics?
Page life expectancy, Page lookups/sec, Page reads/sec, Page writes/sec, Batch Requests/sec, SQL Compilations/sec, SQL Re-Compilations/sec, Active Temp Tables, Temp Tables Creation Rate, Target Server Memory, Total Server Memory, Full Scans/sec, Index Searches/sec, just to mention a few that I usually look at.
Does PerfMon show any problems with disk IO, network IO, % Processor time, or Processor Queue Length?
CODO ERGO SUM |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/26/2010 : 12:18:51
|
I will look now MVJ, thanks.
I can't get the REcompiles to go away on Production.
Staging DB is only a couple of days old. If I switch Brands on Staging there is no recompile. On Production every execution is recompiling.
I have changed the SProc to use @TableVar instead of #TEMP now its all in one Sproc. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/26/2010 : 13:21:34
|
Didn't think it made sense that STAGING was not recompiling but PRODUCTION was.
Two cockups ... the Profiler I was running was filtering on the Child Sproc Name (still used by other things) and I had not noticed it was not filtering on Parent Sproc Name  .
I ran the revised Sproc on the TEST database, and then thought I ran it on PRODUCTION but I hadn't changed DB - so I ran it on TEST again. More, or even Some!, sleep would help ...
Now it is running on PRODUCTION it is not recompiling any more  . But its not noticeably faster.
I'm going to recode a couple of other high usage Sprocs to get rid of Child Sprocs with #TEMP dependency. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/26/2010 : 14:05:27
|
Nigel:
Another Sproc that was calling the same Child Sproc (i.e. using #TEMP between them) was not recompiling. I've changed it anyway, to incorporate the code from the child, instead of calling it, and that has made it faster.
Just wanted to mention it in case you thought it was odd. This is the same Child Sproc that the earlier Parent Sproc was using (and which is no longer recompiling) |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/29/2010 : 06:40:42
|
quote:
Originally posted by Michael Valentine Jones
Have you looked in detail at SQL Server PerfMon statistics?
Took a while, sorry. I could not get Stats to save using a remove machine (all sorts of permission problems - a story for another day), so then gave up and do it on the live machine  . We didn't get a busy spell again until last night. So here is an average figure taken over 5 minutes at the same time on Saturday and Sunday. Sunday was about 5-10 minutes after we got overloaded, but before we had taken any drastic action (such as activating our Server Busy process). There were no backups or scheduled tasks running during this period, so (best as I can tell) this is just user activity.
I have sorted by percentage increase - but some things have fallen dramatically, and some figures were so small on Saturday that the dramatic increase on Sunday may not be relevant.
E: Data and TEMPDB (MDF & LDF)
F: Logs (Out on the SAN only used for staging database, no data transfer in this time interval)
H: Logs (Main database)
G: Backups
Saturday Sunday %age Property
1372.168607 138190.0452 10071% SQLServer: Locks(_Total)\ Lock Wait Time (ms)
10.27586207 876.6 8531% SQLServer: Wait Statistics(Average wait time (ms))\ Lock waits
1.833333333 138.6333333 7562% SQLServer: General Statistics\ Processes blocked
36.81078418 1104.184202 3000% SQLServer: Locks(_Total)\ Average Wait Time (ms)
0.172413793 5.1 2958% SQLServer: Wait Statistics(Average wait time (ms))\ Log write waits
4.379310345 127.0333333 2901% SQLServer: Transactions\ NonSnapshot Version Transactions
4835.034483 100084.5333 2070% SQLServer: Memory Manager\ Granted Workspace Memory (KB)
24.17241379 467.4 1934% SQLServer: Transactions\ Transactions
29.17241379 410.9333333 1409% SQLServer: Plan Cache(_Total)\ Cache Objects in use
6421.789761 41279.22089 643% PhysicalDisk(0 C:)\ Disk Write Bytes/sec
0.003559916 0.017189246 483% SQLServer: Locks(_Total)\ Lock Timeouts/sec
1.793103448 8.066666667 450% SQLServer: Wait Statistics(Waits started per second)\ Page IO latch waits
92.2 391.8333333 425% SQLServer: General Statistics\ User Connections
92.3 392.0666667 425% SQLServer: General Statistics\ Logical Connections
36.23681286 139.8841317 386% SQLServer: Locks(_Total)\ Lock Waits/sec
1703.724138 6557.866667 385% SQLServer: Memory Manager\ Connection Memory (KB)
0.000121728 0.000447577 368% PhysicalDisk(0 C:)\ Avg. Disk Queue Length
0.000121728 0.000447577 368% PhysicalDisk(0 C:)\ Avg. Disk Write Queue Length
0.012172832 0.044757668 368% PhysicalDisk(0 C:)\ % Disk Write Time
0.049864405 0.178833218 359% SQLServer: SQL Statistics\ SQL Re-Compilations/sec
1.75862069 4.8 273% SQLServer: Wait Statistics(Average wait time (ms))\ Non-Page latch waits
1974.816469 4887.714249 248% SQLServer: Latches\ Total Latch Wait Time (ms)
0.6 1.366666667 228% SQLServer: Databases(LIVE)\ Active Transactions
0.666666667 1.433333333 215% SQLServer: General Statistics\ Transactions
0.002453307 0.005148078 210% PhysicalDisk(1 E:)\ Avg. Disk sec/Read
2.128773516 4.17968385 196% SQLServer: Latches\ Average Latch Wait Time (ms)
518.0689655 942.6333333 182% SQLServer: Wait Statistics(Waits started per second)\ Lock waits
5392.649725 9277.401892 172% PhysicalDisk(0 C:)\ Avg. Disk Bytes/Write
0.000219658 0.000355157 162% PhysicalDisk(6 G:)\ Avg. Disk Queue Length
0.000219658 0.000355157 162% PhysicalDisk(6 G:)\ Avg. Disk Write Queue Length
0.021965778 0.035515651 162% PhysicalDisk(6 G:)\ % Disk Write Time
1.41475754 2.219317901 157% Processor(_Total)\ % Privileged Time
7.325271138 11.06631022 151% SQLServer: General Statistics\ Logouts/sec
7.394122477 10.91501285 148% SQLServer: General Statistics\ Logins/sec
23.85375926 34.97005165 147% Processor(_Total)\ % Processor Time
22.44082159 32.74751736 146% Processor(_Total)\ % User Time
1327.965517 1888.333333 142% SQLServer: Wait Statistics(Waits started per second)\ Page latch waits
0.843680758 1.197651356 142% PhysicalDisk(1 E:)\ % Disk Read Time
0.008436808 0.011976514 142% PhysicalDisk(1 E:)\ Avg. Disk Read Queue Length
714.1888484 991.6380542 139% SQLServer: Latches\ Latch Waits/sec
2.413793103 3.3 137% SQLServer: Wait Statistics(Waits started per second)\ Network IO waits
1812.965517 2460.533333 136% SQLServer: Memory Manager\ Optimizer Memory (KB)
0.000253041 0.000339106 134% PhysicalDisk(6 G:)\ Avg. Disk sec/Write
9.89489E-05 0.000122686 124% PhysicalDisk(0 C:)\ Avg. Disk sec/Write
94.32185061 110.4405436 117% SQLServer: Access Methods\ Pages Allocated/sec
5150.241379 6024.833333 117% SQLServer: Plan Cache(_Total)\ Cache Object Counts
37.12705258 42.86364488 115% SQLServer: SQL Errors(_Total)\ Errors/sec
268180.4138 306733 114% SQLServer: Plan Cache(_Total)\ Cache Pages
23.62898357 26.80629059 113% SQLServer: General Statistics\ Connection Reset/sec
26.57969883 29.92907989 113% SQLServer: Access Methods\ Page Splits/sec
4178.758621 4649.066667 111% SQLServer: Memory Manager\ SQL Cache Memory (KB)
0.24137931 0.266666667 110% SQLServer: Wait Statistics(Average wait time (ms))\ Network IO waits
425.9396965 465.5829046 109% SQLServer: SQL Statistics\ Batch Requests/sec
2414.896552 2634.866667 109% SQLServer: Wait Statistics(Waits started per second)\ Non-Page latch waits
826.7395401 899.2384035 109% SQLServer: Databases(LIVE)\ Transactions/sec
543262.3276 590693.8349 109% PhysicalDisk(2 H:)\ Disk Write Bytes/sec
0.015848366 0.017211993 109% PhysicalDisk(1 E:)\ Avg. Disk Queue Length
186608.333 202197.7009 108% SQLServer: Buffer Manager\ Page lookups/sec
480.1300174 519.6989887 108% SQLServer: Databases(LIVE)\ Write Transactions/sec
480.1678217 519.7333881 108% SQLServer: Databases(LIVE)\ Log Flush Waits/sec
1314.418412 1418.460453 108% PhysicalDisk(2 H:)\ Avg. Disk Bytes/Write
544897.7378 584861.9976 107% SQLServer: Databases(LIVE)\ Log Bytes Flushed/sec
195.2857029 209.4827258 107% SQLServer: SQL Statistics\ SQL Compilations/sec
43375.8844 46096.05221 106% PhysicalDisk(1 E:)\ Avg. Disk Bytes/Write
118.7809458 125.34811 106% SQLServer: Workload Group Stats(internal)\ Query optimizations/sec
1403.512988 1473.797375 105% PhysicalDisk(6 G:)\ Disk Write Bytes/sec
1057.533333 1103.633333 104% SQLServer: General Statistics\ Active Temp Tables
3.179097957 3.298555614 104% PhysicalDisk(2 H:)\ % Disk Write Time
0.031790998 0.032985556 104% PhysicalDisk(2 H:)\ Avg. Disk Write Queue Length
0.031795826 0.032985556 104% PhysicalDisk(2 H:)\ Avg. Disk Queue Length
52701.40758 54512.68387 103% SQLServer: Access Methods\ Index Searches/sec
1 1.033333333 103% SQLServer: Databases(LIVE)\ Log Flush Wait Time
7.67892E-05 7.90218E-05 103% PhysicalDisk(2 H:)\ Avg. Disk sec/Write
116553.5454 119114.7591 102% SQLServer: Locks(_Total)\ Lock Requests/sec
6.344827586 6.466666667 102% SQLServer: Wait Statistics(Average wait time (ms))\ Page IO latch waits
0.033333333 0.033333333 100% PhysicalDisk(1 E:)\ Current Disk Queue Length
5376000 5376000 100% SQLServer: Buffer Manager\ Target pages
5376000 5376000 100% SQLServer: Buffer Manager\ Total pages
50808 50808 100% SQLServer: Memory Manager\ Lock Memory (KB)
32319000 32319000 100% SQLServer: Memory Manager\ Maximum Workspace Memory (KB)
43008000 43008000 100% SQLServer: Memory Manager\ Target Server Memory (KB)
43008000 43008000 100% SQLServer: Memory Manager\ Total Server Memory (KB)
99.99978532 99.99898814 100% SQLServer: Buffer Manager\ Buffer cache hit ratio
4845682.759 4830007.467 100% SQLServer: Transactions\ Free Space in tempdb (KB)
83.33529915 82.18865951 99% SQLServer: Databases(LIVE)\ Log Cache Hit Ratio
88.88786827 87.62605881 99% SQLServer: Plan Cache(_Total)\ Cache Hit Ratio
2383.931034 2324.933333 98% SQLServer: Wait Statistics(Waits started per second)\ Log write waits
250.855036 239.6716171 96% SQLServer: Access Methods\ Full Scans/sec
577114.1437 514414.3899 89% PhysicalDisk(1 E:)\ Disk Write Bytes/sec
41666.6 37134 89% SQLServer: Buffer Manager\ Page life expectancy
0.29921643 0.233837445 78% SQLServer: General Statistics\ Temp Tables Creation Rate
6.20653E-05 4.82785E-05 78% PhysicalDisk(5 F:)\ Avg. Disk sec/Write
0.000620716 0.000482731 78% PhysicalDisk(5 F:)\ % Disk Write Time
6.20716E-06 4.82731E-06 78% PhysicalDisk(5 F:)\ Avg. Disk Queue Length
6.20716E-06 4.82731E-06 78% PhysicalDisk(5 F:)\ Avg. Disk Write Queue Length
51.24976435 39.14266661 76% SQLServer: Buffer Manager\ Page writes/sec
2113.805643 1549.85931 73% PhysicalDisk(6 G:)\ Avg. Disk Bytes/Write
0.000251429 0.000183184 73% PhysicalDisk(1 E:)\ Avg. Disk sec/Write
0.741155533 0.523547912 71% PhysicalDisk(1 E:)\ % Disk Write Time
0.007411557 0.005235479 71% PhysicalDisk(1 E:)\ Avg. Disk Write Queue Length
44904.63503 24104.40303 54% PhysicalDisk(1 E:)\ Avg. Disk Bytes/Read
143452.0833 76678.88178 53% PhysicalDisk(1 E:)\ Disk Read Bytes/sec
17.42030893 8.971490333 52% SQLServer: Buffer Manager\ Page reads/sec
194.2068966 17.65517241 9% PhysicalDisk(5 F:)\ Avg. Disk Bytes/Write
19.37355798 1.759995295 9% PhysicalDisk(5 F:)\ Disk Write Bytes/sec
0.708215988 0.037820068 5% SQLServer: Databases(LIVE)\ Log Cache Reads/sec
0.000482731 0 0% PhysicalDisk(2 H:)\ % Disk Read Time
1709.02069 0 0% PhysicalDisk(2 H:)\ Avg. Disk Bytes/Read
4.82731E-06 0 0% PhysicalDisk(2 H:)\ Avg. Disk Read Queue Length
7.2415E-06 0 0% PhysicalDisk(2 H:)\ Avg. Disk sec/Read
1260.14366 0 0% PhysicalDisk(2 H:)\ Disk Read Bytes/sec
0 1.366666667 SQLServer: Wait Statistics(Average wait time (ms))\ Page latch waits
0 0 PhysicalDisk(0 C:)\ % Disk Read Time
0 0 PhysicalDisk(0 C:)\ Avg. Disk Bytes/Read
0 0 PhysicalDisk(0 C:)\ Avg. Disk Read Queue Length
0 0 PhysicalDisk(0 C:)\ Avg. Disk sec/Read
0 0 PhysicalDisk(0 C:)\ Current Disk Queue Length
0 0 PhysicalDisk(0 C:)\ Disk Read Bytes/sec
0 0 PhysicalDisk(2 H:)\ Current Disk Queue Length
0 0 PhysicalDisk(3)\ % Disk Read Time
0 0 PhysicalDisk(3)\ % Disk Write Time
0 0 PhysicalDisk(3)\ Avg. Disk Bytes/Read
0 0 PhysicalDisk(3)\ Avg. Disk Bytes/Write
0 0 PhysicalDisk(3)\ Avg. Disk Queue Length
0 0 PhysicalDisk(3)\ Avg. Disk Read Queue Length
0 0 PhysicalDisk(3)\ Avg. Disk sec/Read
0 0 PhysicalDisk(3)\ Avg. Disk sec/Write
0 0 PhysicalDisk(3)\ Avg. Disk Write Queue Length
0 0 PhysicalDisk(3)\ Current Disk Queue Length
0 0 PhysicalDisk(3)\ Disk Read Bytes/sec
0 0 PhysicalDisk(3)\ Disk Write Bytes/sec
0 0 PhysicalDisk(4)\ % Disk Read Time
0 0 PhysicalDisk(4)\ % Disk Write Time
0 0 PhysicalDisk(4)\ Avg. Disk Bytes/Read
0 0 PhysicalDisk(4)\ Avg. Disk Bytes/Write
0 0 PhysicalDisk(4)\ Avg. Disk Queue Length
0 0 PhysicalDisk(4)\ Avg. Disk Read Queue Length
0 0 PhysicalDisk(4)\ Avg. Disk sec/Read
0 0 PhysicalDisk(4)\ Avg. Disk sec/Write
0 0 PhysicalDisk(4)\ Avg. Disk Write Queue Length
0 0 PhysicalDisk(4)\ Current Disk Queue Length
0 0 PhysicalDisk(4)\ Disk Read Bytes/sec
0 0 PhysicalDisk(4)\ Disk Write Bytes/sec
0 0 PhysicalDisk(5 F:)\ % Disk Read Time
0 0 PhysicalDisk(5 F:)\ Avg. Disk Bytes/Read
0 0 PhysicalDisk(5 F:)\ Avg. Disk Read Queue Length
0 0 PhysicalDisk(5 F:)\ Avg. Disk sec/Read
0 0 PhysicalDisk(5 F:)\ Current Disk Queue Length
0 0 PhysicalDisk(5 F:)\ Disk Read Bytes/sec
0 0 PhysicalDisk(6 G:)\ % Disk Read Time
0 0 PhysicalDisk(6 G:)\ Avg. Disk Bytes/Read
0 0 PhysicalDisk(6 G:)\ Avg. Disk Read Queue Length
0 0 PhysicalDisk(6 G:)\ Avg. Disk sec/Read
0 0 PhysicalDisk(6 G:)\ Current Disk Queue Length
0 0 PhysicalDisk(6 G:)\ Disk Read Bytes/sec
0 0 PhysicalDisk(7)\ % Disk Read Time
0 0 PhysicalDisk(7)\ % Disk Write Time
0 0 PhysicalDisk(7)\ Avg. Disk Bytes/Read
0 0 PhysicalDisk(7)\ Avg. Disk Bytes/Write
0 0 PhysicalDisk(7)\ Avg. Disk Queue Length
0 0 PhysicalDisk(7)\ Avg. Disk Read Queue Length
0 0 PhysicalDisk(7)\ Avg. Disk sec/Read
0 0 PhysicalDisk(7)\ Avg. Disk sec/Write
0 0 PhysicalDisk(7)\ Avg. Disk Write Queue Length
0 0 PhysicalDisk(7)\ Current Disk Queue Length
0 0 PhysicalDisk(7)\ Disk Read Bytes/sec
0 0 PhysicalDisk(7)\ Disk Write Bytes/sec
0 0 SQLServer: Access Methods\ Dropped rowset cleanups/sec
0 0 SQLServer: Access Methods\ Dropped rowsets skipped/sec
0 0 SQLServer: Access Methods\ Table Lock Escalations/sec
0 0 SQLServer: Databases(LIVE)\ Tracked transactions/sec
0 0 SQLServer: Locks(_Total)\ Number of Deadlocks/sec
0 0 SQLServer: Resource Pool Stats(internal)\ Active memory grant amount (KB)
0 0 SQLServer: Resource Pool Stats(internal)\ Cache memory target (KB)
0 0 SQLServer: Resource Pool Stats(internal)\ Compile memory target (KB)
0 0 SQLServer: Resource Pool Stats(internal)\ CPU usage %
0 0 SQLServer: Resource Pool Stats(internal)\ CPU usage target %
0 0 SQLServer: Resource Pool Stats(internal)\ Memory grant timeouts/sec
0 0 SQLServer: Resource Pool Stats(internal)\ Memory grants/sec
0 0 SQLServer: Resource Pool Stats(internal)\ Pending memory grants count
0 0 SQLServer: Resource Pool Stats(internal)\ Query exec memory target (KB)
0 0 SQLServer: Resource Pool Stats(internal)\ Target memory (KB)
0 0 SQLServer: Resource Pool Stats(internal)\ Used memory (KB)
0 0 SQLServer: SQL Statistics\ Auto-Param Attempts/sec
0 0 SQLServer: SQL Statistics\ Failed Auto-Params/sec
0 0 SQLServer: SQL Statistics\ Forced Parameterizations/sec
0 0 SQLServer: SQL Statistics\ Guided plan executions/sec
0 0 SQLServer: SQL Statistics\ Misguided plan executions/sec
0 0 SQLServer: SQL Statistics\ Unsafe Auto-Params/sec
0 0 SQLServer: Transactions\ Snapshot Transactions
0 0 SQLServer: Transactions\ Update Snapshot Transactions
0 0 SQLServer: Wait Statistics(Average wait time (ms))\ Log buffer waits
0 0 SQLServer: Wait Statistics(Average wait time (ms))\ Memory grant queue waits
0 0 SQLServer: Wait Statistics(Average wait time (ms))\ Transaction ownership waits
0 0 SQLServer: Wait Statistics(Waits in progress)\ Lock waits
0 0 SQLServer: Wait Statistics(Waits in progress)\ Log buffer waits
0 0 SQLServer: Wait Statistics(Waits in progress)\ Log write waits
0 0 SQLServer: Wait Statistics(Waits in progress)\ Memory grant queue waits
0 0 SQLServer: Wait Statistics(Waits in progress)\ Network IO waits
0 0 SQLServer: Wait Statistics(Waits in progress)\ Non-Page latch waits
0 0 SQLServer: Wait Statistics(Waits in progress)\ Page IO latch waits
0 0 SQLServer: Wait Statistics(Waits in progress)\ Page latch waits
0 0 SQLServer: Wait Statistics(Waits in progress)\ Transaction ownership waits
0 0 SQLServer: Wait Statistics(Waits started per second)\ Log buffer waits
0 0 SQLServer: Wait Statistics(Waits started per second)\ Memory grant queue waits
0 0 SQLServer: Wait Statistics(Waits started per second)\ Transaction ownership waits
0 0 SQLServer: Workload Group Stats(internal)\ Active parallel threads
0 0 SQLServer: Workload Group Stats(internal)\ Active requests
0 0 SQLServer: Workload Group Stats(internal)\ Blocked tasks
0 0 SQLServer: Workload Group Stats(internal)\ CPU usage %
0 0 SQLServer: Workload Group Stats(internal)\ Queued requests
0 0 SQLServer: Workload Group Stats(internal)\ Requests completed/sec
0 0 SQLServer: Workload Group Stats(internal)\ Suboptimal plans/sec
|
Edited by - Kristen on 11/29/2010 06:45:04 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/29/2010 : 06:56:48
|
Sorry, forgot to log these (which might have been useful)
Memory – Available MBytes
Paging File – % Usage
System – Processor Queue Length
I've added them for next time.
We have some drives in the SAN that are now unused. I wondered about moving them local to the machine (given that the SAN is a bottleneck) and either moving TEMPDB to it (although I don't think we are TEMPDB heavy - do the Stats show that?) or splitting the database onto that drive. Perhaps I could just move our write-hungry Activity Logging Table to that drive.
I'd appreciate your thoughts, thanks. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/29/2010 : 11:14:04
|
I don't see anything to indicate an IO problem, with low Disk Queue Lengths and short Avg. Disk sec/Read and Avg. Disk sec/Write. Memory looks plentiful with a long Page life expectancy. Processor % looks reasonable, and the page lookups, reads, and writes per second look reasonable.
The only thing that looks really bad is Lock Wait Time.
Probably the next step should be to look for which procedures are being blocked and try to determine why they are blocked.
I would also look for any batches that are being executed with non-standard isolation levels, like Repeatable Read or Serializable, to make sure you are always running with Read Committed Snapshot. If you are using connection pooling, the isolation level does not get reset when the API executes sp_reset_connection, so if a transaction uses Repeatable Read or Serializable, the connection will still have that isolation level when it is reused.
http://connect.microsoft.com/SQLServer/feedback/details/243527/sp-reset-connection-doesnt-reset-isolation-level
-- Search for blocking
select
blocked.session_id as blocked_sessionid,
blocked_text.text as blocked_statement,
blocking.session_id as blocking_sessionid,
blocking_text.text as blocking_statement,
blocked.transaction_isolation_level as
blocked_transaction_isolation_level,
blocking.transaction_isolation_level as
blocking_transaction_isolation_level
,*
from
sys.dm_os_waiting_tasks as tasks
join
sys.dm_exec_requests as blocking
on tasks.blocking_session_id = blocking.session_id
join
sys.dm_exec_requests as blocked
on tasks.session_id = blocked.session_id
cross apply
sys.dm_exec_sql_text(blocking.sql_handle) as blocking_text
cross apply
sys.dm_exec_sql_text(blocked.sql_handle) as blocked_text
where
tasks.session_id <> tasks.blocking_session_id
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 11/29/2010 11:44:22 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/29/2010 : 11:54:19
|
Logs haven't grown since created. They are 16GB created for optimise VLBs (thus presume that "Growth" not something to worry about? but appreciate the thought, thanks)
Windows Server 2008 R2 Enterprise
@@Version =
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
"under which OS were the disks created?"
Good question! I saw that blog article and assumed ours would have been created on the machine itself, but its possible they weren't ... I'll check
"I don't see anything to indicate an IO problem. Memory looks plentiful with a long Page life expectancy, processor % looks reasonable, and the page lookups, reads, and writes per second look reasonable."
Yeah, that's my reading of it too. Its peculiar as it does not seem to have run out of CPU, Memory or Disk "ability" but we get a smidgen over 500 TCP connections and its instantly dead.
"The only thing that looks really bad is Lock Wait Time."
Yeah, I spotted that too!
"Probably the next step should be to look for which procedures are being blocked and try to determine why they are blocked."
I did do a big of digging. Nothing is locked for long (as best as I can tell) just an awful lot of things want the same lock - which I guess is a write lock to INSERT a row into out logging Table.
"I would also look for any batches that are being executed with non-standard isolation levels"
Almost 100% certain we don't have any. Everything runs at default isolation. We are using READ_COMMITTED_SNAPSHOT.
"If you are using connection pooling, the isolation level does not get reset when the API executes sp_reset_connection, so if a transaction uses Repeatable Read or Serializable, the connection will still have that isolation level when it is reused."
That's interesting thanks. I am concerned that our connection string from ASP is old. We have not been able to get a more recent one work (weird errors, nothing that the application is catching, feels like some sort of corruption between IIS and SQL)
We are using this:
Provider=sqloledb;Data Source=SSSSSSS;User Id=UUUUUU;Password=XXXXXX;Initial Catalog=DDDDDDD
and we would like to be using this:
Provider=SQLNCLI10;Data Source=SSSSSSS;User Id=UUUUUU;Password=XXXXXX;Initial Catalog=DDDDDDD;"
preferably also with: ConnectionTimeout=15; CommandTimeout=120;
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/29/2010 : 11:58:22
|
Hmmm ... just had a thought, our logging looks like this:
EXEC @LogID = usp_LogInsert(@SomeValue)
... code ...
EXEC usp_LogUpdate(@LogID, @SomeResultCode)
but in some instances that code is in an Sproc that is, itself, called from another Sproc which creates a transaction
EXEC @LogID = usp_LogInsert(@SomeValue)
BEGIN TRANSACTION
... code ...
EXEC usp_MyChildSproc
... code ...
COMMIT or ROLLBACK
EXEC usp_LogUpdate(@LogID, @SomeResultCode)
so when the child usp_MyChildSproc also calls usp_LogInsert(@SomeValue) that is going to Block until the transaction completes presumably?
Perhaps I should look to remove the logging from the Child Sprocs? |
Edited by - Kristen on 11/29/2010 11:59:41 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/29/2010 : 12:33:14
|
| I checked DISKPART and the disks look fine, thanks. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/29/2010 : 12:46:08
|
If logging is not that critical in the short run, you could verify or eliminate it as the cause of your problem by changing your logging procedures to immediately return before taking any action. If the problems goes away, then you know what to look at.
I would definitely eliminate doing any logging inside a transaction. That seems like asking from trouble. If you are calling a procedure inside a transaction and that procedure is the only thing in the transaction, then move the begin and end transaction inside the child procedure and call the logging procedure after the transaction. Also, you might consider putting code in the logging procedures that raises an error and does a return if a transaction is open when it is called (after you have made sure it should not be happening!).
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 11/29/2010 12:55:57 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/29/2010 : 13:12:37
|
"Also, you might consider putting code in the logging procedures that raises an error and does a return if a transaction is open when it is called (after you have made sure it should not be happening!)."
Ace! I've been racking my brains trying to think of what might be the culprit. (These child Sprocs are often called direct too)
For now I will just change the logging to LOG that it was called @@TRANCOUNT set, and within an hour or two I'll know what they are!
(Just doing a NULL action and returning may cause side effects if anything else is relying on the log transaction later, so I'm nervous about rushing to implement that)
Thanks very much. |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 11/29/2010 : 13:22:10
|
why do you update the log table in the first place? wouldn't just inserts suffice?
acso have you thought about just dropping the logging and use the built in auditing? It's fast since it's based on new Extended events.
___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!
SQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
russell
Pyro-ma-ni-yak
USA
4964 Posts |
Posted - 11/29/2010 : 14:04:28
|
Manually logging executions on busy system is one of the worst things you can do, as you essentially double the number of write operations.
Especially inside a transaction. In fact, I'll review the need for each and every explicit transaction. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/29/2010 : 14:27:28
|
quote:
Originally posted by tkizer
quote:
Originally posted by spirit1
acso have you thought about just dropping the logging and use the built in auditing? It's fast since it's based on new Extended events.
We are having major performance issues on a new application that is making heavy use of the auditing feature. We've got a Severity A case open with MS (for over two weeks now).
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog
Is that problem with SQL Server Audit, SQL Server Extended Events, Change Data Capture, or something else?
Using Change Data Capture seems like a good option for the logging if the only thing being tracked is changes to data in tables. Since it happens outside the transaction by using a transaction log reader it should be a very lightweight way to log changes. I haven’t used it myself, so I don’t know if there are any gotchas.
Edit: Never mind, Change Data Capture is an Enterprise Edition feature, and I see you are running Web Edition.
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 11/29/2010 14:35:30 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 11/29/2010 : 14:39:04
|
quote:
Originally posted by Michael Valentine Jones
Is that problem with SQL Server Audit, SQL Server Extended Events, Change Data Capture, or something else?
We are having issues with the pieces of code that are using the change tracking feature. It is being used along with the MS Sync Framework to sync thousands of mobile devices.
If you intend to use this feature, I would highly recommend adding an update stats with fullscan job for the internal change tracking tables. We were at 100% CPU suddenly and after a few hours I opened a SevA case with MS as we had exacerbated our troubleshooting. After a few hours of being on the phone, we were able to eliminate the high CPU utilization by running update stats with fullscan on the internal change tracking tables. We are still having severe performance issues, but at least high CPU utilization has been resolved.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog |
|
|
|
Topic |
|
Performance Problem - Bottleneck
