Percent of completion for some operations in SS2005

Hi folks
I have seen many and many businesses today have already upgraded to SQL Server 2005 and at this time I would like to share with you some of new features that this product gives us.
As you know SQL Server 2005 provides lots of dynamic management views (DMV) to get access to internal behaviour of processes or commands. If I remember well, someone of MVPs has already published about this feature , so anywhere..

Take a look at this script. Run for example BACKUP DATABASE comand and in another session run the below script. It provides you with start time column , percent of complete and what possible completion time is...(estimated_completion_time ) COOL, right?.

SELECT TOP 2 start_time,
percent_complete ,estimated_completion_time
FROM sys.dm_exec_requests
ORDER BY start_time desc

Check it out with those commands

DBCC CHECKDB
DBCC SHRINKDATABASE
DBCC SHRINKFILE
BACKUP DATABASE
ROLLBACK

Tables that do not have Clustered Index

I'd like to share with you this script that returns name of tables that do not have Clustered Index defined on.You may ask, why do we need to create a Clustered Index on the table at all? Well,I think it is a subject for another blog , my experience is that every table should have clustered index.

--SQL Server 2005 (SP2)

SELECT name AS object_name FROM sys.objects WHERE name NOT IN
(
SELECT o.name AS object_name
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
WHERE objectproperty(o.object_id,'IsMSShipped') =0
AND I.type=1 ---Clustered Index
) AND objectproperty(sys.objects.object_id,'IsMSShipped') =0 ---Only user objects
AND objectproperty(sys.objects.object_id,'IsTable') =1 ---Only tables
ORDER BY name

Columns_Updated() within a trigger

Happy New Year to everyone. Once someone asked me about what columns are affected by the UPDATE statement. As you probaly know that in SQL Server 2005 we can do that in more elegant way, but for SQL Server 2000 I decided perfrom some testing.

CREATE TABLE Test (c1 INT NOT NULL PRIMARY KEY, c2 CHAR(1),c3 INT)
GO
--Insert some data
INSERT INTO Test VALUES (1,'A',100)
INSERT INTO Test VALUES (2,'B',500)

--Create a trigger
CREATE TRIGGER tr_my_trigger ON Test FOR UPDATE
AS
DECLARE @ColumnID INT
DECLARE @Columns VARCHAR(8000)
SET @Columns = SPACE(0)
SET @ColumnID = 1
WHILE @ColumnID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Test')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@ColumnID - 1) / 8 + 1, 1)) &
POWER(2, (@ColumnID - 1) % 8) =
POWER(2, (@ColumnID - 1) % 8)
SET @Columns = @Columns + CAST(@ColumnID AS VARCHAR) + ','
SET @ColumnID = @ColumnID + 1
END
PRINT 'Updated columns are :' + @Columns

---
UPDATE Test SET c2='D',c3=2000 WHERE c1=1
--Updated columns are :2,3
We see that the second and the third column were affected according to the INFORMATION_SCHEMA.COLUMNS ordered by ORDINAL_POSITION (check it out). It is not comfortable to see those numbers , but with a little effort you can get also name of columns from the INFORMATION_SCHEMA.COLUMNS.

I suggest you not to run/use that on the production server as it may hurt performance.

Rebuild index, when does it complete?

I have recently worked on the client site (SQL Server 2000) to help on optimization. They have a very huge table (about 250 million rows) and perfom rebuild index every day at night. That jobs has beeen completed in two hours or so till this monday. The job was in 'executin status' for more than 10 hours and imagine what happened to the LOG file. My first thing was it would be really helpfull to have any system table that shows a percentage or whatever about the rebuild index process (something similar to what we have in SQL Server 2005 for some commands (BACKUP DATABASE) to see percent's complete )as I did not intend to KILL the process.Finally we got on and the process was completed. I suggested to the client rebuilding only heavy fragmented indexes especially if you have such huge table. Also , it is unnecessary to rebuild that table every day (it depends on your business requirements) ,so in their case , it is enough once a week.

In SQL Server 2005 for below operations you are able to see completion time or percent of completion.

DBCC CHECKDB
DBCC SHRINKDATABASE
DBCC SHRINKFILE
BACKUP DATABASE
ROLLBACK


SELECT TOP 2 start_time,
percent_complete ,estimated_completion_time
FROM sys.dm_exec_requests
ORDER by start_time desc

Shrink TempDB database

I have been recently worked for some client who has huge tempdb size (SQL Server 2000). As you probably know, you cannot reduce physical size of tempdb without restart MSSQLServices. Using the command DBCC SHRINKFILE to shrink the individual tempdb files you must run DBCC SHRINKFILE command while no other activity occurs in the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single user
mode.
So I would like to show some stepS we ended up. The client completely understands the problem cause tempdb database to be growing and I hope it is not short time solution.

1) Stop SQL Server. Open a command prompt, and then start SQL Server by
typing the following command:

"sqlservr -c -f" (remove the quotation)

The -c and -f parameters force SQL Server to start in a minimum
configuration mode with a tempdb size of 1 MB for the data file and 0.5 MB
for the log file.
Connect to SQL Server with Query Analyzer, and then issue the following
TSQL commands for the tempdb database files that you need to shrink

use tempdb
go

dbcc shrinkfile (tempdev, 'target size in MB') --for the primary data file
or
dbcc shrinkfile (templog, 'target size in MB')--for the log file

As MS says
"An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to
a size smaller than its original size. A limitation of DBCC SHRINKFILE is that you cannot make the database smaller than the size of the model database."

Non-updateable views

As we know from BOL
"Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition"

But I would like to share with you one method to prevent updating view that based on single table. Let's see the following

CREATE TABLE1 (col1 int,col2 int,col3 int,col4 int,col5 int)
GO
INSERT INTO t1 VALUES (1,1,11,2,12)
INSERT INTO t1 VALUES (8,10,15,25,55)
INSERT INTO t1 VALUES (9,1,11,2,5,81)
GO
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1,
col2+0 AS col2,
col3,
col4+0 AS col4,
col5
FROM T1
GO

--Usage

UPDATE v1 SET col1=100 where col2=10
The above statement is succeed, but what happened to the next one

UPDATE v1 SET col2=1000 where col3=15
--Update or insert of view or function 'v1' failed because it contains a derived or --constant field.

You cannot derived column ,as it should be referring to the update, not the view.
In SQL Server 2000, you can handle it using an INSTEAD OF trigger.

Reading Registry

Sometimes we need to read registry by using T-SQL . I would like to show you some scripts to do the job.

--Getting MDAC
EXEC master..xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\DataAccess',
N'Version'

--Getting an account SQL Server runs under
DECLARE @serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'

SELECT @Serviceaccount

---Getting SQL Server path's installation and the data path
DECLARE @DataPath nvarchar( 512 ) , @SQLPath nvarchar( 512 ) ,
@ToolsPath nvarchar( 512 )
EXECUTE sp_MSget_setup_paths @SQLPath OUT , @DataPath OUT;
PRINT 'SQLPath : ' + QUOTENAME( @SQLPath , '"');
PRINT 'DataPath : ' + QUOTENAME( @DataPath , '"');