Browse by Tags

DBCC EXTENTINFO - unused pages in these extents (undocumented)

Published 4 September 7 7:45 AM | SSQA.net

When it comes to resolving table fragmentation, the basic checkout you perform is to run DBCC INDEXDEFRAG or even run DBCC DBREINDEX statements. As per the default configuraiton SQL Database Engine allocates a new extent to an allocation unit only when...

Filed under: sql server, performance, indexes, data, i/o, table, insert, allocation, fragmentation, deadlock, extent

Triggers within CLR - advantage over TSQL

Published 4 September 7 4:10 AM | SSQA.net

You may be aware the DML and DDL triggers can be nested up to 32 levels, because any reference to such trigger code counts as one-level in the nesting limit. Even though it is possible to control whether AFTER triggers can be nested through the nested...

Filed under: sql server, performance, query, clr, trigger, table, tsql, recursive, cte

Performance - check whether it is affecting application or system, don't blame SQL Server alone

Published 3 September 7 9:42 AM | SSQA.net

Its a general assumption that whenever performance is degraded, the finger will be pointed to SQL Server, pretty easy eh!? If you look at any of the SQL Server related forums then 3 in 10 questions asks same question and nothing but shove blame on SQL...

Filed under: application, sql server, performance, blocking, security, query, table, wait, queue, high cpu, oltp

Rebuild some fragmented indexes - problem with Extent Scan Fragmentation value

Published 24 August 7 6:24 AM | SSQA.net

This was the question from SSP forums, I’m trying to rebuild few fragmented indexes on a table that is updated on regular basis. The “dbcc showcontig” gave this result: Table: 'Confidential' (999999999); index ID: 5, database ID: 5 LEAF level...

Filed under: sql server, performance, security, dbcc, index, table, fragmentation, showcontig, defrag

Error: Could not find row in sysindexes for database. 8966, 823 and 602

Published 14 August 7 4:35 AM | SSQA.net

Long ago, not long ago.... No doubt that many of you might have gone through the error above within your SQL environment, also I see many forum posts out there to resolve the issue. The bottom line of this issue is Hardware and no other issue can contribute...

Filed under: sql server, performance, database, dbcc, checkdb, table, hardware, corrupted, sysindexes, 8966, 823, page

DTS package, how will optimizer treats the transactions?

Published 2 August 7 7:52 AM | SSQA.net

With the SQL Server 2000 DTS has very good capability of treating the huge processes as transactions, let's assume these parameters: DTS Package with "Use Transactions" and "Commit on successful package completion" boxes checked...

Filed under: performance, dts, table, statistics, transactions, join

Table fragmentation, Index contention and locking issues to resolve

Published 31 July 7 9:31 AM | SSQA.net

Yet another important factors that every DBA needs to concerned about table & index fragmentation within their SQL Server environment. Refer to the a rticle about DetectTableFragmentation in both 2000 and 2005 version. Fragmentation occurs due to...

Filed under: sql server, profiler, performance, blocking, trace, process, table, locking, loss, fragmentation

How do I find all the statistics and statistics columns on a specified object?

Published 16 July 7 7:46 AM | SSQA.net

USE <database_name>; GO SELECT s.name AS statistics_name ,c.name AS column_name ,sc.stats_column_id FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id INNER JOIN sys.columns AS c ON...

Filed under: sql server, performance, indexes, table, tsql, statistics, columns

SQL Server Cursors usage and performance - cursed

Published 19 June 7 9:34 AM | SSQA.net

If you have a requirement to poll through 'n' number of rows then immediately you would think about Cursors in SQL Server. As the solution using any programming language that loops recordsets to build the attendance list that works ok, but really...

Filed under: sql server, performance, databases, dmv, hints, usage, cursor, table