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 , '"');

How many times stored procedures have recompiled?

I have recently visited our client and we found out that He has a very high number of stored procedures that have recompiled. Certainly, we have observed some perfomance decreasing. Fortunately , the client has alredy upgraded to SQL Server 2005(SP2) and apart from running SQL Server Profile to indentify those stored procedures we came out with the following query.

It gives you the top 10 stored procedures that have been recompiled.

select *
from sys.dm_exec_query_optimizer_info

select top 10
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc

Note that the plan_generation_num indicates the number of times the stored procedure has recompiled. Very cool feature.

RESTORE LOG file and WITH FILE behaviour

Recently I have been visited one of the our clients and have been asked the following question.
"What if I have 3 fileid within a backup file and when restore the log being done, I do not specify WITH FILE option,so do I get latest Log's data?". To answer the question I decide to conduct some test script.


CREATE DATABASE demo
GO
ALTER DATABASE demo SET RECOVERY FULL
GO
CREATE TABLE demo..t1(c1 INT NOT NULL PRIMARY KEY)
--insert one row
insert demo..t1 VALUES (1)
--BACKUP the data
BACKUP DATABASE demo TO DISK = 'c:\temp\demo.bak' WITH INIT
GO
insert demo..t1 VALUES (2)
--start BACKUP log data
BACKUP log demo TO DISK = 'c:\temp\demo_log.bak' WITH INIT
GO
insert demo..t1 VALUES (3)
--- BACKUP log FILE at this time WITH FILE =2
BACKUP log demo TO DISK = 'c:\temp\demo_log.bak' WITH NOINIT
GO
insert demo..t1 VALUES (4)
--- BACKUP log FILE at this time WITH FILE =3
BACKUP log demo TO DISK = 'c:\temp\demo_log.bak' WITH NOINIT

--Lets do RESTORE and see what is going on
RESTORE DATABASE demo FROM DISK = 'c:\temp\demo.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG demo FROM DISK = 'c:\temp\demo_log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG demo FROM DISK = 'c:\temp\demo_log.bak' WITH RECOVERY ----Do you expect the last one?

GO
SELECT * FROM demo..t1
c1
-----------
1
2
It did not return rows 3 and 4.

So that means if you DO NOT specify WITH FILE in RESTORE LOG file you DO NOT get latest FILEID but only the first one.