SqlServer-QA.net

Fragmentation is dearest friend of database when you need to deal with Performance, so by using the DBCC statements and other methods here you can deal the database level fragmentation, what about the physical level fragmentation?   So what is the best way forward to fix the data file fragmentation in SQL Server, say when you have much of ...

Continuing on the MAX WORKER THREADS topic from ThisBlog I have been asked about method of calculating the amount of system resources that will be used when the setting is increased to 500 from 255 (default). Well, as one of the best practices you must test the implications and usage in hiking such a value that will affect the whole server ...

This is a subject I posted about last year on my old blog but it came up at SQL Connections last week several times so I want to repost it for those who’ve just started following my blog. There's only one time when you should be trying to work out how long a CHECKDB is going to take - when you're planning your regular database ...

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

DBCC UPDATEUSAGE (0);GO Best practice to use DBCC UPDATEUSAGE intermittently and must if you have upgraded the database from SQL 2000 to 2005. The usual working fashion of this DBCC statement is to correct the rows, used pages, reserved pages, leaf pages, and data page counts for each partition in a table or index. If there are no ...

In SQL Server 2005 Dynamic Management Views (DMV) & DM Functions (DMF) allows you the look inside the server to return the required information. It will also helps you to monitor the server instance health and performance to diagnose the problem. Database administrators familiar with previous versions of SQL Server ...

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

Yet anothe forums related question I would like to blog, as I have seen in my experience on newsgroups. There will be always a question asking about why Transaction log is filling up and blocking is ocurred when a DBREINDEX & CHECKDB processes are under execution. Well the reason is obvious that these DBCC statements are ...