May 19, 2010 at 3:35 pm
GilaMonster (5/19/2010)
Do you have a reason to think the size of the system tables is adversely effecting DB performance?
I think this application is not creating hundreds but thousands tables:Wow:, but it drop most of them and keep some of them in database. I do see lots of KEY lock on system tables and top wait types lay on PAGELATCH_EX and PAGELATCH_SH.
No Signature
May 19, 2010 at 8:12 pm
You can capture DDL commands through an even trace or by looking at the default trace on the server. Based on that volume, the default trace might be rolling over fairly quickly, I'm not sure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2010 at 12:40 am
Yang-703993 (5/19/2010)
GilaMonster (5/19/2010)
Do you have a reason to think the size of the system tables is adversely effecting DB performance?I think this application is not creating hundreds but thousands tables:Wow:, but it drop most of them and keep some of them in database. I do see lots of KEY lock on system tables and top wait types lay on PAGELATCH_EX and PAGELATCH_SH.
The page latch waits may very well be contention on the allocation pages.
Do you see blocking from those key locks? The wait types would be lock waits, not page latch.
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
May 20, 2010 at 7:40 am
Here is top 3 wait type and time summary, and it looks like nothing to do with lock from here.
wait_type PAGELATCH_SH PAGELATCH_EX SOS_SCHEDULER_YIELD
waiting_tasks_count 266282883 316009039 644608081
wait_time_ms 22415617031 13232498406 9612024281
max_wait_time_ms 6875 7406 2750
signal_wait_time_ms 1464081500 485230375 9611863218
per%362115
No Signature
May 20, 2010 at 7:57 am
Could you do some checks and see what the page latch waits are commonly on? The wait_resource in sys.dm_exec_requests. I have a suspicion that I want to verify.
How many files/filegroups in this database? How many processor cores does the server have?
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
May 20, 2010 at 8:19 am
GilaMonster (5/20/2010)
Could you do some checks and see what the page latch waits are commonly on? The wait_resource in sys.dm_exec_requests. I have a suspicion that I want to verify.How many files/filegroups in this database? How many processor cores does the server have?
This view is just snapshoot, but I bring one result here. Also, this DB only have one filegroup.
commandtotal_elapsed_timestatus
DROP TABLE8524suspended
CONDITIONAL8396runnable
DROP TABLE8244suspended
DROP TABLE8243running
DROP TABLE8208runnable
DROP TABLE8071runnable
DROP TABLE7837runnable
DROP TABLE7835running
UPDATE7832runnable
DROP TABLE7832running
No Signature
May 20, 2010 at 8:56 am
That's not what I asked for. Need the wait_type and wait_resources please for processes that are waiting (ie suspended).
How many CPUs on this system? Is there only 1 file in that filegroup?
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
May 20, 2010 at 9:10 am
GilaMonster (5/20/2010)
That's not what I asked for. Need the wait_type and wait_resources please for processes that are waiting (ie suspended).How many CPUs on this system? Is there only 1 file in that filegroup?
This is what happened moment ago, and it is ordered by total_elapsed_time, thanks!
commandtotal_elapsed_timewait_typewait_resource
CREATE TABLE5000PAGELATCH_EX5:1:11099981
CREATE TABLE2805PAGELATCH_SH5:1:11099981
CREATE TABLE5082PAGELATCH_EX5:1:11099981
CREATE TABLE4874PAGELATCH_EX5:1:11099981
CREATE TABLE714PAGELATCH_EX5:1:11099981
CREATE TABLE4099PAGELATCH_EX5:1:11099981
CREATE TABLE5047PAGELATCH_EX5:1:11099981
CREATE TABLE5045PAGELATCH_EX5:1:11099981
CREATE TABLE4870PAGELATCH_EX5:1:11099981
CREATE TABLE5212PAGELATCH_SH5:1:11099981
No Signature
May 20, 2010 at 9:42 am
Could you run this and post the results? It's an undocumented command to look at a database page's raw structure (in this case, just the header)
DBCC TRACEON(3604)
DBCC PAGE (5,1,11099981)
DBCC TRACEOFF(3604)
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
May 20, 2010 at 9:49 am
GilaMonster (5/20/2010)
Could you run this and post the results? It's an undocumented command to look at a database page's raw structure (in this case, just the header)
DBCC TRACEON(3604)DBCC PAGE (5,1,11099981)
DBCC TRACEOFF(3604)
Here is it,
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:11099981)
BUFFER:
BUF @0x00000006A2FC6680
bpage = 0x00000006A219A000 bhash = 0x0000000000000000 bpageno = (1:11099981)
bdbid = 5 breferences = 0 bUse1 = 6958
bstat = 0xec20009 blog = 0xbb797979 bnext = 0x0000000000000000
PAGE HEADER:
Page @0x00000006A219A000
m_pageId = (1:11099981) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 1 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 13 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 851968
Metadata: PartitionId = 851968 Metadata: IndexId = 1 Metadata: ObjectId = 13
m_prevPage = (1:10791655) m_nextPage = (1:750831) pminlen = 19
m_slotCnt = 62 m_freeCnt = 6794 m_freeData = 7411
m_reservedCnt = 0 m_lsn = (2754097:208:226) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 1006698561
Allocation Status
GAM (1:10735872) = ALLOCATED SGAM (1:10735873) = NOT ALLOCATED
PFS (1:11096736) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:10735878) = CHANGED
ML (1:10735879) = NOT MIN_LOGGED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
No Signature
May 20, 2010 at 10:06 am
Interesting, not quite what I was expecting.
That's a table that stores info on what columns exist in what objects. Latches protect the physical structure of the page, so that two processes can't both modify page header or structure and mess up the result.
I don't think there's any way you're going to fix this. It's not about the size of the system tables, it's about how fast they are getting modified.
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
May 20, 2010 at 10:55 am
GilaMonster (5/20/2010)
Interesting, not quite what I was expecting.That's a table that stores info on what columns exist in what objects. Latches protect the physical structure of the page, so that two processes can't both modify page header or structure and mess up the result.
I don't think there's any way you're going to fix this. It's not about the size of the system tables, it's about how fast they are getting modified.
That is why I am looking for maintain system tables, but it looks like mission impossible. I will try to create more files for this database to see how much it can help.
Again, thanks Gail and everyone helped here.
No Signature
May 20, 2010 at 11:01 am
Yang-703993 (5/20/2010)
That is why I am looking for maintain system tables, but it looks like mission impossible.
Won't help here. As I said, it's not about the size of the system tables. The latch waits come from the frequency of changes. Could very likely get this with small number of tables if there's the same speed of create/drop.
I will try to create more files for this database to see how much it can help.
Might, if you also have allocation contention (quite likely). Start with creating additional files in primary so that you have number of files = 1/4 the number of cpu cores.
More files will alleviate latch contention on the allocation pages (if you run DBCC Page, a page with a type of 9 (sgam), but probably not latch contention on pages in the system tables
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
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply