Optimizations on System Tables

Hi

I have an issue where my system tables are heavily fragmented. Currently our system creates between 200 and 400 new logins daily (its the method the application handles it security). However we have had execution times on this of between 13ms and 1900ms, very inconsistent.

I have seen in a previous thread http://dbforums.com/arch/70/2003/11/965415
that its Kalen Delaney's opinion that fragmentation doesn't really matter unless you have 'several dozen' pages

Here is an example of our sysxlogins table's report :

DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 162
- Extents Scanned.......................: 35
- Extent Switches.......................: 156
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 13.38% [21:157]
- Logical Scan Fragmentation ..............: 49.38%
- Extent Scan Fragmentation ...............: 34.29%
- Avg. Bytes Free per Page................: 5694.6
- Avg. Page Density (full)................: 29.64%

As you can see, its got 162 pages, which probably counts as 'several' dozen.

I would like to know if its possible for us to defrag/reindex this table, as you can see, it has a VERY low Scan Density, and a rather high Fragmentation level.

Opinions, help, undocumented functionality all welcome
TiaanI am facing a same issue.
I have noticed that my system table are extremly fragmented. Take a look. Any suggestions? Or does it not matter that they are like that?

DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 23
- Extents Scanned.......................: 7
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 3.3
- Scan Density [Best Count:Actual Count]......: 13.64% [3:22]
- Logical Scan Fragmentation ..............: 43.48%
- Extent Scan Fragmentation ...............: 71.43%
- Avg. Bytes Free per Page................: 2744.6
- Avg. Page Density (full)................: 66.09%

DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3019.0
- Avg. Page Density (full)................: 62.70%

DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 85
- Extents Scanned.......................: 17
- Extent Switches.......................: 81
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 13.41% [11:82]
- Logical Scan Fragmentation ..............: 44.71%
- Extent Scan Fragmentation ...............: 70.59%
- Avg. Bytes Free per Page................: 3348.5
- Avg. Page Density (full)................: 58.63%

DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 159
- Extent Switches.......................: 769
- Avg. Pages per Extent..................: 6.2
- Scan Density [Best Count:Actual Count]......: 15.97% [123:770]
- Logical Scan Fragmentation ..............: 46.24%
- Extent Scan Fragmentation ...............: 18.24%
- Avg. Bytes Free per Page................: 3261.0
- Avg. Page Density (full)................: 59.71%

DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 3
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3771.5
- Avg. Page Density (full)................: 53.40%

DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 29
- Extents Scanned.......................: 8
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 14.81% [4:27]
- Logical Scan Fragmentation ..............: 48.28%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3096.8
- Avg. Page Density (full)................: 61.74%

DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 4
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 2.5
- Scan Density [Best Count:Actual Count]......: 28.57% [2:7]
- Logical Scan Fragmentation ..............: 60.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3522.2
- Avg. Page Density (full)................: 56.48%

DBCC SHOWCONTIG scanning 'sysmessages' table...
Table: 'sysmessages' (36); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 169
- Extents Scanned.......................: 23
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 7.3
- Scan Density [Best Count:Actual Count]......: 81.48% [22:27]
- Logical Scan Fragmentation ..............: 1.78%
- Extent Scan Fragmentation ...............: 13.04%
- Avg. Bytes Free per Page................: 3926.9
- Avg. Page Density (full)................: 51.48%

DBCC SHOWCONTIG scanning 'syslanguages' table...
Table: 'syslanguages' (44); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.5
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3206.0
- Avg. Page Density (full)................: 60.39%

DBCC SHOWCONTIG scanning 'syscharsets' table...
Table: 'syscharsets' (45); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 33
- Extents Scanned.......................: 6
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.5
- Scan Density [Best Count:Actual Count]......: 62.50% [5:8]
- Logical Scan Fragmentation ..............: 24.24%
- Extent Scan Fragmentation ...............: 16.67%
- Avg. Bytes Free per Page................: 2896.6
- Avg. Page Density (full)................: 64.21%

DBCC SHOWCONTIG scanning 'sysaltfiles' table...
Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 14.29%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 4283.4
- Avg. Page Density (full)................: 47.08%

DBCC SHOWCONTIG scanning 'spt_values' table...
Table: 'spt_values' (85575343); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 5
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
- Logical Scan Fragmentation ..............: 28.57%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2671.7
- Avg. Page Density (full)................: 66.99%|||You need not worry about fragmentation on System tables and use DBCC UPDATEUSAGE in order to correct the inconsistency.

OPENXML performance

Hi,
we use heavily openxml to insert data into our sqlserver 2000 sp3 tables.
We have some performance issues that seems to be caused by our use of OPENXML.
Doing a trace and wait analisys (1) we found that much of the time is taken
by oledb operation.
Looking at the trace file we saw a remote scan appening any time we do an
xpath query over openxml.
We managed to do all the openxml work at the sp start, putting all the data
in table variables (in memory), freeing the xml doc and then using those
tables to do the work.
Looking at docs we didn't find great infos about some aspects of OPENXML.
OPENXML uses an OLEDB rowset to do the work, so it seems that it loads the
xml in a MSXML dom and then populates the rowset.
Looking at the sqlserver proc it seems that it uses MSXML2, so looking at
the version of it we found 8.2.7919.0.
This seems a 2.6 SP2 version.
Is it possible/recommended to upgrade the version to 2.5 sp3?
Is it possible/recommended to upgrade the version to MSXML3 or MSXML4?
It seems also that there's a buffer where those dom documents are stored.
Is it possible to trace how much documents/space are keeped by mssql?
There's some optimization to set up with OPENXML?
To pass the xml fragment to mssql we use mssql oledb, we can have some
advantage by using sqlxmloledb?
Carlo Folini
(1) http://support.microsoft.com/default...B;EN-US;271509
See below.
Best regards
Michael
"Carlo Folini" <folini@.community.nospam> wrote in message
news:A01A902E-799B-44E5-8D9B-54813547B769@.microsoft.com...
> Hi,
> we use heavily openxml to insert data into our sqlserver 2000 sp3 tables.
> We have some performance issues that seems to be caused by our use of
> OPENXML.
> Doing a trace and wait analisys (1) we found that much of the time is
> taken
> by oledb operation.
> Looking at the trace file we saw a remote scan appening any time we do an
> xpath query over openxml.
> We managed to do all the openxml work at the sp start, putting all the
> data
> in table variables (in memory), freeing the xml doc and then using those
> tables to do the work.
> Looking at docs we didn't find great infos about some aspects of OPENXML.
> OPENXML uses an OLEDB rowset to do the work, so it seems that it loads the
> xml in a MSXML dom and then populates the rowset.
> Looking at the sqlserver proc it seems that it uses MSXML2, so looking at
> the version of it we found 8.2.7919.0.
> This seems a 2.6 SP2 version.
Correct.

> Is it possible/recommended to upgrade the version to 2.5 sp3?
Do you mean 2.6 SP3? It is not supported to do that upgrade yourself. There
are some issues with running the MSI if SP2 is already installed.

> Is it possible/recommended to upgrade the version to MSXML3 or MSXML4?
No. Although the next SQL Server 2000 service pack is probably moving to use
MSXML 3.

> It seems also that there's a buffer where those dom documents are stored.
> Is it possible to trace how much documents/space are keeped by mssql?
Unfortunately not. The rule of thumb is 3 to 6 times as much as the original
textual size.

> There's some optimization to set up with OPENXML?
There are some best practices (some you are already following above). In
addition, you want to avoid parent axis (..) unless needed, and do not use
flag 3.

> To pass the xml fragment to mssql we use mssql oledb, we can have some
> advantage by using sqlxmloledb?
Not from the OpenXML point of view.
What are the symptoms that you see and what is the doc size and load
characteristics (feel free to contact me per email).
Best regards
Michael
> --
> Carlo Folini
> (1) http://support.microsoft.com/default...B;EN-US;271509
|||inline...

> Do you mean 2.6 SP3? It is not supported to do that upgrade yourself. There
> are some issues with running the MSI if SP2 is already installed.
Yes, I meant 2.6 sp3. The sp2 version is different from the 'official one'
published on the support site.
We have 8.2.7919.0 instead of 8.2.8307.0.
Do you think that we have to update our sp2 installation?
> There are some best practices (some you are already following above). In
> addition, you want to avoid parent axis (..) unless needed, and do not use
> flag 3.
What do you mean for "flag 3"?
Having the following xml structure/query, how can we avoid parent axis?
SELECT Address
FROM OPENXML(@.HDoc, 'MyData/Tab/Row', 1)
WITH (MyTableName VARCHAR(18) '../@.TableName',
Address VARCHAR(120))
WHERE MyTableName = 'Pippo'
<MyData>
<Tab MyTableName="Pippo">
<Row address="sdfdsf fds"/>
<Row address="432 432243"/>
<Row address="bcv bvccb bvc"/>
</Tab>
</MyData>

> What are the symptoms that you see and what is the doc size and load
> characteristics (feel free to contact me per email).
>
We have some critical conditions that we are investigating, in those
situation the CPU% grows to 100%.
Having the cpu to 100% the time taken by each sp grows, causing the lock to
be held for long time.
So we saw in our log that we have a number of timeouts and also some
deadlock conditions.
The timeouts are throwed by the sp that uses openxml.
The xml stream (passed as text parameter to the sp) ranges from 6k to 60k.
The data are essentially an xml representation of some tables content (the
data are retrieved from host via COMTI and serialized to xml by a vb6
component).
Thanks for you help
Carlo
|||See below.
Best regards
Michael
"Carlo Folini" <folini@.community.nospam> wrote in message
news:96468399-2219-4418-A80B-B9D30D52E2D2@.microsoft.com...
> inline...
> Yes, I meant 2.6 sp3. The sp2 version is different from the 'official one'
> published on the support site.
> We have 8.2.7919.0 instead of 8.2.8307.0.
> Do you think that we have to update our sp2 installation?
The support site is a bit newer, since it includes some additional bug
fixes. I don't think they help you with your perf issues.

> What do you mean for "flag 3"?
With the syntax OPENXML( handle, row expression, flag) flag is the third
argument. It should not be set to 3 for performance reasons with MSXML 2.6
any version (MSXML 3.0 should have fixed the problem, still 3 should be
avoided if possible).

> Having the following xml structure/query, how can we avoid parent axis?
> SELECT Address
> FROM OPENXML(@.HDoc, 'MyData/Tab/Row', 1)
> WITH (MyTableName VARCHAR(18) '../@.TableName',
> Address VARCHAR(120))
> WHERE MyTableName = 'Pippo'
> <MyData>
> <Tab MyTableName="Pippo">
> <Row address="sdfdsf fds"/>
> <Row address="432 432243"/>
> <Row address="bcv bvccb bvc"/>
> </Tab>
> </MyData>
You cannot, unless you can copy the attribute to its children when the XML
is generated or along the way (ie, mid-tier). Note that SQL Server SP4
should not have this problem.

> We have some critical conditions that we are investigating, in those
> situation the CPU% grows to 100%.
> Having the cpu to 100% the time taken by each sp grows, causing the lock
> to
> be held for long time.
> So we saw in our log that we have a number of timeouts and also some
> deadlock conditions.
> The timeouts are throwed by the sp that uses openxml.
> The xml stream (passed as text parameter to the sp) ranges from 6k to 60k.
> The data are essentially an xml representation of some tables content (the
> data are retrieved from host via COMTI and serialized to xml by a vb6
> component).
I assume that the XPath execution will take utilization up to that level.
The document size does not look too bad though. How many concurrent
transactions are using OpenXML at the same time?
Also, deadlocks normally also indicate a problem with respect to your update
logic. Can you take the OpenXML part out of the transactions that deadlock
and see what happens then?

> Thanks for you help
> Carlo
Best regards
Michael