Presentation Links: SQL Server Performance Tuning (Quest)

http://blogs.msdn.com/b/buckwoody/archive/2010/07/21/presentation-links-sql-server-performance-tuning-quest.aspx

Grant create table and SP for schema

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'CSODMGMT\SQLREWR')
CREATE USER [CSODMGMT\SQLREWR] FOR LOGIN [CSODMGMT\SQLREWR]
GO
EXEC sp_addrolemember N'db_datareader', N'CSODMGMT\SQLREWR'
GO
EXEC sp_addrolemember N'db_datawriter', N'CSODMGMT\SQLREWR'
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SQLREWR')
DROP SCHEMA [SQLREWR]
GO
CREATE SCHEMA [SQLREWR] AUTHORIZATION [CSODMGMT\SQLREWR]
GO
GRANT CREATE TABLE TO [CSODMGMT\SQLREWR]
go
GRANT CREATE PROCEDURE TO [CSODMGMT\SQLREWR]
go


----------------------------------
declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'CSODMGMT\SQLREWR'

DECLARE Cur_name CURSOR FOR
select specific_name From information_Schema.routines

OPEN Cur_name
FETCH NEXT FROM Cur_name INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
print '================================='
print @name
print '================================='
set @string ='GRANT VIEW DEFINITION ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string

set @string ='GRANT EXECUTE ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string



FETCH NEXT FROM Cur_name INTO @name
END
CLOSE Cur_name
DEALLOCATE Cur_name
GO

sp_msforeachdb

sp_msforeachdb
'
if ''?'' like ''ces_%''
begin
use [?]
print ''use [''+''?''+'']''
print ''GO''
print ''DROP FULLTEXT INDEX ON lo_text''
print ''go''
end
'

Find Currently Running Query

-- ALL Sessions
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

-- SPECIFIC SESSION DETAIL
dbcc inputbuffer(59)

Implementing the OUTPUT Clause in SQL Server 2008

http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/?utm_source=simpletalk&utm_medium=email-main&utm_content=OutputClause-20100726&utm_campaign=SQL

Batch File Commands

From the old days, sometimes batch file becomes very handy

http://academic.evergreen.edu/projects/biophysics/technotes/program/batch.htm

sp_MSforeachtable

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount order by rowcnt desc
select * from #rowcount
drop table #rowcount

Transparent Data Encryption in SQL Server 2008

http://www.sql-server-performance.com/articles/dba/Transparent_Data_Encryption_p1.aspx

http://www.mssqltips.com/tip.asp?tip=1507

http://msdn.microsoft.com/en-us/library/bb677274.aspx


You encounter SQL Server error 17308 after you enable TDE on one of your databases in SQL Server

Moving System Databases - A Checklist

Moving System Databases
Moving a database in SQL Server is normally as simple as detaching it from the server, moving it to it's new location and then attaching it again. However, that only counts for user databases. Moving system databases requires quite a bit more work. Since I had to do this a couple of times during the last weeks I wrote down a simple checklist that I could follow to make sure it was done right, so I did not screw up the servers and could speed up the process as much as possible. And, since I guess I will be doing it again sometime in the future and inevitably will lose the notes I scribbled down, I thought I could just as well post them here for safe-keeping. If anyone wants to use this checklist go ahead, but remember to do the steps in the exact order of the list, and make sure you have all the necessary backups before starting. To be completely clear, doing this wrong can completely screw up your databases and I can not take any responsibility if anything does go wrong. Also note that the checklist was written for the specific situation I was encountered with. Your system databases might have more data files and/or other file paths and names than those in the list, so you might need to make some changes. Have fun!

Make sure you have backups of all user databases plus master, model and msdb.
Moving msdb and model
In Enterprise Manager, right-click the server and choose Properties.
Click Startup Parameters.
Add a new parameter "-T3608" (without the quotes)
Stop SQL Server.
Start SQL Server, and make sure that SQL Agent is NOT started.
Run the following command in Query Analyzer:
-------------
use master
go
exec sp_detach_db 'msdb'
go
exec sp_detach_db 'model'
go
-------------

Move the data and log files for both msdb (normally msdbdata.mdf and msdblog.ldf) and model (normally model.mdf and modellog.mdf) to their new locations.
Run the following in Query Analyzer:
-------------
use master
go
exec sp_attach_db 'model'
, 'PATH_TO_MODEL_DATAFILE\model.mdf'
, 'PATH_TO_MODEL_LOGFILE\modellog.ldf'
go
-------------

Remove the -T3608 flag in Enterprise Manager/Server/Properties/Startup Parameters.
Stop SQL Server.
Start SQL Server.
Run the following in Query Analyzer and check that the file paths are correct:
-------------
use model
go
exec sp_helpfile
go
-------------

Run the following in Query Analyzer:
-------------
use master
go
exec sp_attach_db 'msdb'
, 'PATH_TO_MSDB_DATAFILE\msdbdata.mdf'
, 'PATH_TO_MSDB_LOGFILE\msdblog.ldf'
go
-------------

Run the following in Query Analyzer and check that the file paths are correct:
-------------
use msdb
go
exec sp_helpfile
go
-------------

Finished!
Moving tempdb
Run the following in Query Analyzer:
-------------
use master
go
alter database tempdb modify file (name = tempdev
, filename = 'PATH_TO_NEW_LOCATION_OF_TEMPDB_DATAFILE\tempdb.mdf')
go
alter database tempdb modify file (name = templog
, filename = 'PATH_TO_NEW_LOCATION_OF_TEMPDB_DATAFILE\templog.ldf')
go
-------------

Stop SQL Server
Start SQL Server
Run the following in Query Analyzer and check that the file paths correspond to those stated in step 1:
-------------
use tempdb
go
exec sp_helpfile
go
-------------

Stop SQL Server.
Move the original data and log files for tempdb to some new location, or rename them, just so that you are sure that SQL Server can not be using them.
Start SQL Server.
Run the following in Query Analyzer and check that no error occurs:
-------------
use tempdb
go
create table test (a int)
insert into test (a) values (1)
select * from test
drop table test
go
-------------

Remove the original data and log files for tempdb.
Finished!

NOTE: Steps 5 through 8 are of course not really necessary, they are just included as a quick extra check to really make sure that nothing went wrong. Skip them if you wish.
Moving master

Note: In this scenario we are not only moving the master database, we are also moving all of the files that SQL Server uses in it's 'data location' (as specified when installing SQL Server). The situation I encountered was that SQL Server's data location was specified to be something like D:\ (though with the program files as normal on C:\Program Files\Microsoft SQL Server\), but now the entire D:\ drive needed to be removed, so we needed to move everything SQL Server had stored there plus all references to it to avoid problems in the future. If you are only moving the master database you only need to follow the applicable steps of course.
In Enterprise Manager, right-click the server and choose Properties.
Click Startup Parameters.
Remove all of the three parameters that are already there (if there are more, remove the three that correspond to the three below in step 4).
Add the three following parameters:
-dPATH_TO_NEW_LOCATION_OF_MASTER_MDFFILE\master.mdf
-ePATH_TO_NEW_LOCATION_OF_SQLAGENT_ERRORLOG\ERRORLOG
-lPATH_TO_NEW_LOCATION_OF_MASTER_LOGFILE\mastlog.ldf

In my case the values of these parameters where as follows:
-dE:\MSSQL\Data\master.mdf
-eE:\MSSQL\LOG\ERRORLOG
-lE:\MSSQL\Data\mastlog.ldf
Stop SQL Server.
Move the files as specified below:
OLD_PATH_TO_MASTER_MDFFILE\master.mdf --> NEW_PATH_TO_MASTER_MDFFILE\master.mdf
OLD_PATH_TO_MASTER_LOGFILE\Data\mastlog.ldf --> NEW_PATH_TO_MASTER_LOGFILE\mastlog.ldf
OLD_PATH_TO_SQL_DATA_LOCATION\BACKUP --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\JOBS --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\LOG --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\REPLDATA --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)

Make the following changes to the registry (using regedit):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
BackupDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\BACKUP

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Replication
WorkingDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\REPLDATA

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
SQLDataRoot = NEW_PATH_TO_SQL_DATA_LOCATION\

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent
ErrorLogFile = NEW_PATH_TO_SQL_DATA_LOCATION\LOG\SQLAGENT.OUT
WorkingDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\JOBS

Note: This checklist does not cover servers using full-text search. If your server does use FT, then you will need to expand steps 6 and 7. Just move the FT directory in step 6 and search the registry for any references to it and change them as necessary.
Start SQL Server.
Finished!
If you are using SQL Agent on your server do not forget to check that it is running.
Conclusion
So there we are, all system databases moved. Again, please note that this was mainly meant as a checklist for myself, but feel free to use it as a base for your own checklist when you need to move system databases. I urge you to read through it several times so you are sure what it says and what you are doing. Most of the steps here come from the Microsoft article Moving SQL Server databases to a new location with Detach/Attach, which will probably help you more than this list. I simply compiled them into an easy-to-follow, step-by-step list that I could use to cut the down-time as much as possible.

By Christoffer Hedgate, 2004/11/30

Multi-statement Table Valued Function vs Inline Table Valued Function