Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks"

Simple vs Searched CASE statements

Rename Server Name for SQL Server Cluster

Fundamentals of Storage Systems, IO Latency and SQL Server

How to: Connect to the Database Engine Using sqlcmd.exe

ADO Data Type mapping between Visual Basic, Access, SQL Server, Oracle, and the .NET Framework.

set nocount on

declare @SqlInstanceName varchar(128),
@SqlServiceName varchar(128),
@Cmd varchar(8000),
@TempDbLoc varchar(255),
@SysDbLoc varchar(255),
@UserDbDataLoc varchar(255),
@UserDbLogLoc varchar(255)


--sp_helpdb 'DBA'
--select * from master..sysaltfiles
--select cast(serverproperty('instancename') as varchar(128))
select @TempDbLoc = 'T:SQL_TempDb'
,@SysDbLoc = 'D:SQL_Data'
,@UserDbDataLoc = 'D:SQL_Data'
,@UserDbLogLoc = 'L:SQL_Logs'

--service name
if cast(serverproperty('instancename') as varchar(128)) is null
begin
set @SqlServiceName = 'MSSQLSERVER'

end
else
begin
set @SqlServiceName = 'MSSQL$' + upper(cast(serverproperty('instancename') as varchar(128)))

end



/********************************************************************************/
--print parameters
select
'--Server: ' + cast(serverproperty('ServerName') as varchar(128))
select
'--@TempDbLoc: ' + @TempDbLoc +
'
--@SysDbLoc: ' + @SysDbLoc +
'
--@UserDbDataLoc: ' + @UserDbDataLoc +
'
--@UserDbLogLoc: ' + @UserDbLogLoc



/********************************************************************************/
--tempdb
select '--Tempdb: '
union all
select '--Old file location: ' + ltrim(rtrim(name)) + ' - ' + ltrim(rtrim([filename]))
--select *
from master..sysaltfiles
where [dbid] = db_id(N'tempdb')
union all
select 'alter database [' + db_name([dbid]) + ']
modify file (NAME = ' + ltrim(rtrim(name)) + ', filename = ''' + ltrim(rtrim(@TempDbLoc + right([filename], patindex('%%', reverse([filename]))))) + ''');
go

'
from master..sysaltfiles
where [dbid] = db_id(N'tempdb');



/********************************************************************************/
--system dbs
select '--System Dbs: '
union all
select '--Old file location: ' + name + ' - ' + [filename]
from master..sysaltfiles
where [dbid] in (
db_id(N'msdb'),
db_id(N'model'),
db_id(N'distribution')
)
union all
select 'alter database [' + db_name([dbid]) + ']
modify file (NAME = ' + ltrim(rtrim(name)) + ', filename = ''' + ltrim(rtrim(@SysDbLoc + right([filename], patindex('%%', reverse([filename]))))) + ''');
go

'
from master..sysaltfiles
where [dbid] in (
db_id(N'msdb'),
db_id(N'model'),
db_id(N'distribution')
);



/********************************************************************************/
--master db
select '--master (startup parameters): '
union all
select
'--Current location:
-- ' + ltrim(rtrim([filename])) +
'
--New location:
-- ' + ltrim(rtrim(@SysDbLoc + right([filename], patindex('%%', reverse([filename]))))) as NewFileLoc
from master..sysaltfiles
where [dbid] = db_id(N'master');



/********************************************************************************/
--resource db (2005 only)
if (@@microsoftversion / 0x1000000) < 10
begin

select '--Resource db (2005 only): '
union all
select '--1. Stop the service;
'
union all
select '--2. Run "NET START ' + @SqlServiceName + ' /f /T3608"
'
union all
select '--3. Move resource db files to the master db location: ' + @SysDbLoc + '\n'
union all
select '--4. alter database mssqlsystemresource modify file (name = data, filename = ''' + @SysDbLoc + '' + 'mssqlsystemresource.mdf'');
'
union all
select '--5. alter database mssqlsystemresource modify file (name = log, filename = ''' + @SysDbLoc + '' + 'mssqlsystemresource.ldf'');
'
union all
select '--6. alter database mssqlsystemresource set read_only;
'
union all
select '--7. Run "NET STOP ' + @SqlServiceName + '"
'
union all
select '--8. Run "NET START ' + @SqlServiceName + '"
'

end



/********************************************************************************/
--detach user dbs
select '--Detach user dbs: '
union all
select
'alter database [' + name + ']
set single_user with rollback immediate
exec sp_detach_db ''' + name + ''', ''true'';
go

'
from master..sysdatabases
where [name] not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)



/********************************************************************************/
--old user db location
--select '--Old user db location'
--union all
--select '/*'
--union all
--select db_name([dbid]) + ': ' + [filename]
--from master..sysaltfiles
--where db_name([dbid]) not in (
-- N'master',
-- N'msdb',
-- N'model',
-- N'distribution',
-- N'tempdb'
--)
--and [fileid] <> 65537
--union all
--select '*/'


--attach user dbs
if (@@microsoftversion / 0x1000000) < 9
begin

select Cmd as [--Attach user dbs]
from (
select [dbid] * 1000 + 0 as RId,
'exec sp_attach_db
@dbname = ''' + db_name([dbid]) + '''' as Cmd
from sysdatabases
where name not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)

union all

select [dbid] * 1000 + max([fileid]) + 1 as RId,
'go
'
from master..sysaltfiles
where db_name([dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and [fileid] <> 65537
group by [dbid]

union all

select sf.[dbid] * 1000 + sf.[fileid] as RId,
' --' + sf.[filename] +
'
,@filename' + cast(sf.[fileid] as varchar(10)) + ' = ''' + case
when sf.[status] & 64 = 64 then @UserDbLogLoc +
case when sff.files_cnt > 2 then '' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%%', reverse(sf.[filename])))
else @UserDbDataLoc +
case when sff.files_cnt > 2 then '' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%%', reverse(sf.[filename])))
end + ''''
from master..sysaltfiles sf
inner join (
select [dbid]
,count(fileid) as files_cnt
from master..sysaltfiles
group by [dbid]
) sff
on sf.[dbid] = sff.[dbid]
where db_name(sf.[dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and sf.[fileid] <> 65537
) q
order by RId

end --> SQL 2000
else
begin

select Cmd as [--Attach user dbs]
from (
select [dbid] * 1000 + 0 as RId,
'create database [' + db_name([dbid]) + ']
on' as Cmd
from master..sysdatabases
where name not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)

union all

select [dbid] * 1000 + max([fileid]) + 1 as RId,
'
for attach
go
'
from master..sysaltfiles
where db_name([dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and [fileid] <> 65537
group by [dbid]

union all

select sf.[dbid] * 1000 + sf.[fileid] as RId,
' --' + sf.[filename] +
'
' + case sf.[fileid] when 1 then '' else ',' end +
'(filename = ''' + case
when sf.[status] & 64 = 64 then @UserDbLogLoc +
case when sff.files_cnt > 2 then '' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%%', reverse(sf.[filename])))
else @UserDbDataLoc +
case when sff.files_cnt > 2 then '' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%%', reverse(sf.[filename])))
end + ''')'
from master..sysaltfiles sf
inner join (
select [dbid]
,count(fileid) as files_cnt
from master..sysaltfiles
group by [dbid]
) sff
on sf.[dbid] = sff.[dbid]
where db_name(sf.[dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and sf.[fileid] <> 65537
) q
order by RId

end --> SQL 2005 and higher


set nocount off

set nocount on

declare @SqlInstanceName varchar(128),
@SqlServiceName varchar(128),
@Cmd varchar(8000),
@TempDbLoc varchar(255),
@SysDbLoc varchar(255),
@UserDbDataLoc varchar(255),
@UserDbLogLoc varchar(255)


--sp_helpdb 'DBA'
--select * from master..sysaltfiles
--select cast(serverproperty('instancename') as varchar(128))
select @TempDbLoc = 'T:\SQL_TempDb'
,@SysDbLoc = 'D:\SQL_Data'
,@UserDbDataLoc = 'D:\SQL_Data'
,@UserDbLogLoc = 'L:\SQL_Logs'

--service name
if cast(serverproperty('instancename') as varchar(128)) is null
begin
set @SqlServiceName = 'MSSQLSERVER'

end
else
begin
set @SqlServiceName = 'MSSQL$' + upper(cast(serverproperty('instancename') as varchar(128)))

end



/********************************************************************************/
--print parameters
select
'--Server: ' + cast(serverproperty('ServerName') as varchar(128))
select
'--@TempDbLoc: ' + @TempDbLoc +
'
--@SysDbLoc: ' + @SysDbLoc +
'
--@UserDbDataLoc: ' + @UserDbDataLoc +
'
--@UserDbLogLoc: ' + @UserDbLogLoc



/********************************************************************************/
--tempdb
select '--Tempdb: '
union all
select '--Old file location: ' + ltrim(rtrim(name)) + ' - ' + ltrim(rtrim([filename]))
--select *
from master..sysaltfiles
where [dbid] = db_id(N'tempdb')
union all
select 'alter database [' + db_name([dbid]) + ']
modify file (NAME = ' + ltrim(rtrim(name)) + ', filename = ''' + ltrim(rtrim(@TempDbLoc + right([filename], patindex('%\%', reverse([filename]))))) + ''');
go

'
from master..sysaltfiles
where [dbid] = db_id(N'tempdb');



/********************************************************************************/
--system dbs
select '--System Dbs: '
union all
select '--Old file location: ' + name + ' - ' + [filename]
from master..sysaltfiles
where [dbid] in (
db_id(N'msdb'),
db_id(N'model'),
db_id(N'distribution')
)
union all
select 'alter database [' + db_name([dbid]) + ']
modify file (NAME = ' + ltrim(rtrim(name)) + ', filename = ''' + ltrim(rtrim(@SysDbLoc + right([filename], patindex('%\%', reverse([filename]))))) + ''');
go

'
from master..sysaltfiles
where [dbid] in (
db_id(N'msdb'),
db_id(N'model'),
db_id(N'distribution')
);



/********************************************************************************/
--master db
select '--master (startup parameters): '
union all
select
'--Current location:
-- ' + ltrim(rtrim([filename])) +
'
--New location:
-- ' + ltrim(rtrim(@SysDbLoc + right([filename], patindex('%\%', reverse([filename]))))) as NewFileLoc
from master..sysaltfiles
where [dbid] = db_id(N'master');



/********************************************************************************/
--resource db (2005 only)
if (@@microsoftversion / 0x1000000) < 10
begin

select '--Resource db (2005 only): '
union all
select '--1. Stop the service;
'
union all
select '--2. Run "NET START ' + @SqlServiceName + ' /f /T3608"
'
union all
select '--3. Move resource db files to the master db location: ' + @SysDbLoc + '\
'
union all
select '--4. alter database mssqlsystemresource modify file (name = data, filename = ''' + @SysDbLoc + '\' + 'mssqlsystemresource.mdf'');
'
union all
select '--5. alter database mssqlsystemresource modify file (name = log, filename = ''' + @SysDbLoc + '\' + 'mssqlsystemresource.ldf'');
'
union all
select '--6. alter database mssqlsystemresource set read_only;
'
union all
select '--7. Run "NET STOP ' + @SqlServiceName + '"
'
union all
select '--8. Run "NET START ' + @SqlServiceName + '"
'

end



/********************************************************************************/
--detach user dbs
select '--Detach user dbs: '
union all
select
'alter database [' + name + ']
set single_user with rollback immediate
exec sp_detach_db ''' + name + ''', ''true'';
go

'
from master..sysdatabases
where [name] not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)



/********************************************************************************/
--old user db location
--select '--Old user db location'
--union all
--select '/*'
--union all
--select db_name([dbid]) + ': ' + [filename]
--from master..sysaltfiles
--where db_name([dbid]) not in (
-- N'master',
-- N'msdb',
-- N'model',
-- N'distribution',
-- N'tempdb'
--)
--and [fileid] <> 65537
--union all
--select '*/'


--attach user dbs
if (@@microsoftversion / 0x1000000) < 9
begin

select Cmd as [--Attach user dbs]
from (
select [dbid] * 1000 + 0 as RId,
'exec sp_attach_db
@dbname = ''' + db_name([dbid]) + '''' as Cmd
from sysdatabases
where name not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)

union all

select [dbid] * 1000 + max([fileid]) + 1 as RId,
'go
'
from master..sysaltfiles
where db_name([dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and [fileid] <> 65537
group by [dbid]

union all

select sf.[dbid] * 1000 + sf.[fileid] as RId,
' --' + sf.[filename] +
'
,@filename' + cast(sf.[fileid] as varchar(10)) + ' = ''' + case
when sf.[status] & 64 = 64 then @UserDbLogLoc +
case when sff.files_cnt > 2 then '\' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%\%', reverse(sf.[filename])))
else @UserDbDataLoc +
case when sff.files_cnt > 2 then '\' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%\%', reverse(sf.[filename])))
end + ''''
from master..sysaltfiles sf
inner join (
select [dbid]
,count(fileid) as files_cnt
from master..sysaltfiles
group by [dbid]
) sff
on sf.[dbid] = sff.[dbid]
where db_name(sf.[dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and sf.[fileid] <> 65537
) q
order by RId

end --> SQL 2000
else
begin

select Cmd as [--Attach user dbs]
from (
select [dbid] * 1000 + 0 as RId,
'create database [' + db_name([dbid]) + ']
on' as Cmd
from master..sysdatabases
where name not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)

union all

select [dbid] * 1000 + max([fileid]) + 1 as RId,
'
for attach
go
'
from master..sysaltfiles
where db_name([dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and [fileid] <> 65537
group by [dbid]

union all

select sf.[dbid] * 1000 + sf.[fileid] as RId,
' --' + sf.[filename] +
'
' + case sf.[fileid] when 1 then '' else ',' end +
'(filename = ''' + case
when sf.[status] & 64 = 64 then @UserDbLogLoc +
case when sff.files_cnt > 2 then '\' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%\%', reverse(sf.[filename])))
else @UserDbDataLoc +
case when sff.files_cnt > 2 then '\' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%\%', reverse(sf.[filename])))
end + ''')'
from master..sysaltfiles sf
inner join (
select [dbid]
,count(fileid) as files_cnt
from master..sysaltfiles
group by [dbid]
) sff
on sf.[dbid] = sff.[dbid]
where db_name(sf.[dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and sf.[fileid] <> 65537
) q
order by RId

end --> SQL 2005 and higher


set nocount off

SSAS performance counters


* Direct hit ratio - Ratio of cache direct hits to cache lookups, for the period between obtaining counter values. Higher is better.
* Evictions / sec - Rate of evictions from the cache. This is per partition per cube per database. Typically due to background cleaner. Could indicate memory issue is the cache is being excessively cleaned.
* Misses / sec - Rate of cache misses. High ratio indicates that queries are having to go to disk
* Current Connections - Current number of client connections established.
* Current user sessions - Current number of user sessions established.
* Total calculation covers - Total number of of calculation covering subcubes in MDX query plan. High value means that queries are being resolved cell by cell instead of block operations.
* Total cells calculated - Total number of cell properties calculated. High value means queries being resolved cell by cell.
* Total recomputes - Total number of cells recomputed due to error
* Memory Usage KB - Memory usage of the server process. Same as perfmon counter for ProcessPrivateBytes.
* Dimension queries / sec - high number means heavy trips to the storage engine
* Measure group queries / sec - high number means heavy trips to the storage engine
* Available Mbytes - Available MBytes is the amount of physical memory available to processes running on the computer
* Page Faults / sec - high page faults / sec could indicate inadequate memory. Counter has both hard and soft page faults. Soft page faults are okay. Hard page faults require disk access and can hinder performance.
* % Processor Time - > 80 % constant value indicates inadequate processor power
* Avg. Disk Queue Length - disk queues occur when the disk is busy reading or writing information. High disk queue lengths indicate inadequate disk performance. Rule of thumb is Avg. Disk Queue Length < 3 for an extended period.