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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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% 36 21 15
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.
command total_elapsed_time status
DROP TABLE 8524 suspended
CONDITIONAL 8396 runnable
DROP TABLE 8244 suspended
DROP TABLE 8243 running
DROP TABLE 8208 runnable
DROP TABLE 8071 runnable
DROP TABLE 7837 runnable
DROP TABLE 7835 running
UPDATE 7832 runnable
DROP TABLE 7832 running
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!
command total_elapsed_time wait_type wait_resource
CREATE TABLE 5000 PAGELATCH_EX 5:1:11099981
CREATE TABLE 2805 PAGELATCH_SH 5:1:11099981
CREATE TABLE 5082 PAGELATCH_EX 5:1:11099981
CREATE TABLE 4874 PAGELATCH_EX 5:1:11099981
CREATE TABLE 714 PAGELATCH_EX 5:1:11099981
CREATE TABLE 4099 PAGELATCH_EX 5:1:11099981
CREATE TABLE 5047 PAGELATCH_EX 5:1:11099981
CREATE TABLE 5045 PAGELATCH_EX 5:1:11099981
CREATE TABLE 4870 PAGELATCH_EX 5:1:11099981
CREATE TABLE 5212 PAGELATCH_SH 5: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