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...

Here is top 3 wait type and time summary, and it looks like nothing to do with lock from here.

wait_type PAGELATCH_SH ...

I think this application is not creating hundreds but thousands tables:Wow:, but...

Gail, how do I monitor allocating structures activities and their empact on I/O performance? Thanks in advance.

GilaMonster (5/19/2010)

---

CirquedeSQLeil (5/19/2010)

---

My inclination would be that the 100's of tables added...

CirquedeSQLeil (5/19/2010)

---

I just ran sp_updatestats to verify, and I do show in my results that it did run against the system tables. Also, here is an article that backs...

As I mentioned, hundreds tables fly in and drop off each day, and that is why I concern about system tables. I know this is out off relational database concept,...

I don't know... If you see data like this

Table Namesysobjvalues

Row Count3213517

Reserved (MB)5740.40625

Data (MB)4807.484375

Index Size (MB)22.7890625

Unused Space (MB)910.1328125

You are right. However, what should I do with these tables?

create table #tablesize

(

tablenamenvarchar(128),

rowchar(11),

reserved varchar(18),

datavarchar(18),

indexsize Varchar(18),

unusedvarchar(18)

)

DECLARE @Tablename sysname

DECLARE @sql nvarchar(200)

DECLARE Table_Cursor CURSOR FOR SELECT [name]

FROM sys.sysobjects

WHERE [type] = 'S'

...

Sample:

Table NameRow Count

sysobjvalues3211810

syshobtcolumns4000161

sysrowsetcolumns4000413

The problem I am facing is that the system tables are too big. I don't know whether they are fragment or not, and I don't know whether I should do...

I understand MS doesn't want DBA to manually working on system tables. But how are these tables maintained?

Most time we set DB mail SMTP Authentication to be Anonymous authentication since we don't want set email accounts for every SQL Server running on different account.

However, there is one...