sql server time out and memory dumps

Question

sql server time out and memory dumps

sqlstar2011

SSCertifiable

Points: 6000
More actions
May 13, 2011 at 8:03 am

#237402

I am running trace on a database. Once trace is complete I see bunch of time outs. So I ran dbcc page statement for 1:1209901 in test database:
DBCC PAGE('test',1,1209901,3) WITH TABLERESULTS
GO
and I am seeing lot of lines where object column(second column) says Memory Dump. here is a sample of what I see:
Slot 0 Offset 0x60 Length 74Memory Dump @0x0000000037DBC0600000000000000000: 30003f00 07000000 8060fe06 00000000 02010000 †0.?......`..........
Slot 0 Offset 0x60 Length 74Memory Dump @0x0000000037DBC0600000000000000014: 50413030 32313534 31bd7f5a 16605e51 4992c992 †PA0021541..Z.`^QI...
Slot 0 Offset 0x60 Length 74Memory Dump @0x0000000037DBC0600000000000000028: 83772319 f8010000 00000000 00000000 e065513b †.w#..............eQ;
Slot 0 Offset 0x60 Length 74Memory Dump @0x0000000037DBC060000000000000003C: 00000107 00a20100 4a003b08 1400†††††††††††††††........J.;...
Slot 0 Offset 0x60 Length 74Slot 0 Column 0 Offset 0x46 Length 4UNIQUIFIER1312827
How do I go about tackling this issue?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Have you checked out the logs?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

sqlstar2011 (5/13/2011)
How do I go about tackling this issue?

If you've run the trace as you say you have, then you have a list of the queries that are causing the timeouts. Now, you can mess around with indexes and server optimizations until you're blue in the face and, unless you really get lucky (for example: finding a very low cardinality index and removing it so you don't have to tolerate its page/extent splits), won't have any success in preventing the timeouts.

99.9% of the time, there is one and only one way to fix such problems... rewrite the code so that it's truly set based.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'm kinda curious as to why you ran DBCC Page. What that returns is the contents of the data page. Other than the object id and index id (which are in the header) there's little else of value in there.

Now, you have the trace so you have the statements that are timing out, so you need to optimise them. The contents of a particular data page are of little to no use in that. Check that the queries can use indexes, check that you have indexes optimal for the queries

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

The memory dump is simply the internal binary representation of the row - the raw data as it is stored on the page. In the DBCC PAGE output in the format you specified, it is followed by the column values as cracked from the dump.

Eddie Wuerch
MCM: SQL