SqlServer-QA.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 it cannot quickly find a page in an existing extent with sufficient space to hold the row being ...

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 triggers server configuration option. So how it can be escaped using CLR and how ...

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 Server. It is always better to be proactive than reactive, when it comes to identifying and ...

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: 5LEAF level scan performed.- Pages Scanned................................: 86- Extents ...

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 such a problem. So this is where DBA's role is enhanced to take care of ...

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. No transaction related boxes checked in the Workflow Properties of each Package step. So the ...

Yet another important factors that every DBA needs to concerned about table & index fragmentation within their SQL Server environment. Refer to the article about DetectTableFragmentation  in both 2000 and 2005 version.   Fragmentation occurs due to updates and delets on the table and the golden rule is that ...

USE <database_name>;GOSELECT s.name AS statistics_name    ,c.name AS column_name    ,sc.stats_column_idFROM sys.stats AS sINNER JOIN sys.stats_columns AS sc     ON s.object_id = sc.object_id AND s.stats_id = sc.stats_idINNER JOIN sys.columns AS c     ON sc.object_id = c.object_id ...

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 say if you have hundreds of thousands rows to fetch then Cursors are not good option. I have ...

In SQL 2000 version days you could take help of DBCC SHOWCONTIG statement, but this is deprecated in SQL Server 2005, so how to go about it. You can take help DMVs & DMFs in this case, DMV - Dynamic Management View and DMF - Dynamic Management Function, both of them help DBAs to determine & discover the database system usage. DMVs and ...