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 Name sysobjvalues

Row Count 3213517

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

(

tablename nvarchar(128),

row char(11),

reserved varchar(18),

data varchar(18),

indexsize Varchar(18),

unused varchar(18)

)

DECLARE @Tablename sysname

DECLARE @sql nvarchar(200)

DECLARE Table_Cursor CURSOR FOR SELECT [name]

FROM sys.sysobjects

WHERE [type] = 'S'

ORDER BY...

Sample:

Table Name Row Count

sysobjvalues 3211810

syshobtcolumns 4000161

sysrowsetcolumns 4000413

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