SQL System tables : master..sysperfinfo

Using the sysperfinfo table for server statistics, broken down by database.
Not sure where a colleague got this script from, so apologies if it's yours.
If it is, get in touch and i'll link you!

select  DB = a.instance_name,
  'DBCC Logical Scans' = a.cntr_value,
  'Transactions/sec' = (select d.cntr_value 
                          from 
                            master..sysperfinfo d 
                          where 
                            d.object_name = a.object_name and 
                            d.instance_name = a.instance_name and 
                            d.counter_name = 'Transactions/sec'),
  'Active Transactions' = (select case when i.cntr_value < 0 then 0 else i.cntr_value end from master..sysperfinfo i 
                          where 
                            i.object_name = a.object_name and 
                            i.instance_name = a.instance_name and 
                            i.counter_name = 'Active Transactions'),
  'Bulk Copy Rows' =      (select b.cntr_value from master..sysperfinfo b 
                          where 
                            b.object_name = a.object_name and
       b.instance_name = a.instance_name and 
                            b.counter_name = 'Bulk Copy Rows/sec'),
  'Bulk Copy Throughput'= (select c.cntr_value from  master..sysperfinfo c 
                          where 
                            c.object_name = a.object_name and 
                            c.instance_name = a.instance_name and 
                            c.counter_name = 'Bulk Copy Throughput/sec'),
  'Log Cache Reads' =     (select e.cntr_value from master..sysperfinfo e 
                          where 
                            e.object_name = a.object_name and 
                            e.instance_name = a.instance_name and 
                            e.counter_name = 'Log Cache Reads/sec'),
  'Log Flushes' =         (select f.cntr_value  from   master..sysperfinfo f 
                          where
       f.object_name = a.object_name and 
                            f.instance_name = a.instance_name and 
                            f.counter_name = 'Log Flushes/sec'),
  'Log Growths' =         (select g.cntr_value  from  master..sysperfinfo g 
                          where 
                            g.object_name = a.object_name and 
                            g.instance_name = a.instance_name and 
                            g.counter_name = 'Log Growths'),
  'Log Shrinks' =         (select h.cntr_value  from  master..sysperfinfo h 
                          where 
                            h.object_name = a.object_name and 
                            h.instance_name = a.instance_name and 
                            h.counter_name = 'Log Shrinks')
from 
    master..sysperfinfo a
where   
    a.object_name like '%Databases%'  and
    a.instance_name <> '_Total' and
    a.counter_name = 'DBCC Logical Scan Bytes/sec'
order by 
    1 

Disk Formatting & Partition Alignment for SQL Server

Cluster Size

SQL Server reads and writes in 64K blocks.

Therefore , format drives you provision for SQL data & logs with a cluster size of 64K.

Simple Really :)

Partition Alignment

Before you format the drives however, there is the matter of Partition Alignment.

The following formulae are published by Microsoft to help determine partition alignment.

Partition_Offset / Stripe_Unit_Size = integer (this is the most important)


Stripe_Unit_Size / File_Allocation_Unit_Size   = integer

In the absence of the stripe size info, the alignment should at the very least be changed from it’s 32K default when establishing the partition.

If we can’t find Stripe size information, the best we can do is use an alignment value of 1024K (1MB) which is common to many SANs and is compatible with the 64K Cluster size.

NB : Alignment is handled automatically on Windows 2008.

IT Knowledgebase : How much performance are you losing my not aligning your drives?
MSDN : Disk Partition Alignment Best Practices for SQL Server


To set alignment >

C:\>DISKPART

Microsoft DiskPart version 5.1.3565

Copyright (C) 1999-2003 Microsoft Corporation.
On computer: DEV008

DISKPART>SELECT DISK 1

Disk 1 is now the selected disk

DISKPART>CREATE PARTITION PRIMARY ALIGN=1024

DiskPart succeeded in creating the specified partition


You'll then want to format the drive in a 64K cluster size. 

23/07/2009 Update - Brilliant post on Disk Alignment in Windows 2008

fsutil - File system utility

fsutil - File system utility is used to return info on/configure the file system...

fsutil fsinfo ntfsinfo d:




Microsoft Windows [Version 6.0.6001]
Copyright (c) 2006 Microsoft Corporation. All rights reserved.

C:\Windows\system32> fsutil fsinfo ntfsinfo d:
NTFS Volume Serial Number : 0x5668a93268a911b3
Version : 3.1
Number Sectors : 0x00000000257fe7ff
Total Clusters : 0x0000000004affcff
Free Clusters : 0x0000000003f31c8e
Total Reserved : 0x0000000000000000
Bytes Per Sector : 512
Bytes Per Cluster : 4096
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x00000000000d0000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x000000000257fe7f
Mft Zone Start : 0x00000000000c00c0
Mft Zone End : 0x00000000000cc8e0
RM Identifier: 58C77029-1F68-11DE-A9C8-00155D14160A

C:\Windows\system32>

FSUTIL

OpenXML over WDDX (Web Distributed Data eXchange) data

DECLARE @xmlstring VARCHAR(MAX)
DECLARE @idoc int
DECLARE @doc xml

SET @xmlstring ='
YESanyJamesBall67877767689711631004117268767hhkjhkjuk  '


SET @doc = CONVERT(XML,@xmlstring)
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

select 
 a.id,
 a.localname, 
 b.id,
 b.parentid,
 b.localname, 
 c.id,
 c.parentid,
 c.localname, 
 d.id,
 d.parentid, 
 d.text as dataproperty, 
 f.text as datavalue
from 
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) a
inner join
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) b
 on b.parentid = a.id
inner join
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) c
 on c.parentid = b.id
inner join
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) d
 on d.parentid = c.id
left join 
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) e
 on e.parentid = b.id
left join
 (SELECT * FROM   
  OPENXML (@idoc, 'wddxPacket/data/struct', 1)) f
 on f.parentid = e.id
where c.localname = 'name'
  and e.localname <> 'name'

USP_RandomLoad

Script to generate a random load by multiplying the results of 2 random tables! -

CREATE PROCEDURE USP_RandomLoad AS
/*
Util    : USP_RandomLoad

Purpose : Generate Random load on a server.

How? :    Randomly selects 2 tables from current database
          and multiply their results together.

   It could easily generate no load if one of the tables
   contains no rows.

Use :     EXECUTE USP_RandomLoad

    Place the procedure in a loop to execute multiple times
    and purposely 'stress' a server.
*/
SET NOCOUNT ON
/* Declare Variables */
DECLARE @SQL VARCHAR(200)
DECLARE @TableCount INT
DECLARE @InnerTableNumber INT
DECLARE @InnerCounter INT
DECLARE @OuterTableNumber INT
DECLARE @OuterCounter INT
DECLARE @VarTable1Schema VARCHAR(200)
DECLARE @VarTable2Schema VARCHAR(200)
DECLARE @VarTable1Name VARCHAR(200)
DECLARE @VarTable2Name VARCHAR(200)

/* Get the total number of tables */
SELECT @TableCount = COUNT(*) FROM INFORMATION_SCHEMA.TABLES

/* Randomly select a outer table by table number */
SELECT @OuterTableNumber = ROUND(RAND() * @TableCount,0,1)
IF @OuterTableNumber = 0 SET @OuterTableNumber = 1
IF @OuterTableNumber > @TableCount SET @OuterTableNumber = @TableCount

SET @OuterCounter = 0

/* Declare a cursor and iterate through all the 
   tables in the current database in tablename order. */
DECLARE CURSORNAME CURSOR FOR
SELECT TABLE_SCHEMA,TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
    SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
OPEN CURSORNAME
FETCH NEXT FROM CURSORNAME 
INTO @VarTable1Schema, @VarTable1Name
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @OuterCounter = @OuterCounter + 1
 IF @OuterCounter = @OuterTableNumber
 /* Random Table found */
  BEGIN
  SET @InnerCounter = 0

  /* Randomly select a inner table by table number */
  SELECT @InnerTableNumber = ROUND(RAND() * @TableCount,0,1)
  IF @InnerTableNumber = 0 SET @InnerTableNumber = 1
  IF @InnerTableNumber > @TableCount SET @InnerTableNumber = @TableCount

  /* Inner cursor to loop through the tables until it gets
     to the Nth one, i.e. the one that corresponds to the 
     random number created */
  DECLARE INNERCURSORNAME CURSOR FOR
  SELECT TABLE_SCHEMA,TABLE_NAME 
  FROM INFORMATION_SCHEMA.TABLES
   SELECT *  FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
  OPEN INNERCURSORNAME
  FETCH NEXT FROM INNERCURSORNAME 
  INTO @VarTable2Schema, @VarTable2Name
  WHILE @@FETCH_STATUS = 0
  BEGIN
   SET @InnerCounter = @InnerCounter + 1
   IF @InnerCounter = @InnerTableNumber
    /* Random Table found */
    BEGIN
    /* Generate a random recordset by using CROSS APPLY to multiply the 2 tables. 
       The record set could be ANY size hence a totally random load is generated. */
    SET @SQL = 'SELECT * FROM ['+@VarTable1Schema+'].['+@VarTable1Name+'] TABLE1 CROSS APPLY ['+@VarTable2Schema+'].['+@VarTable2Name+'] TABLE2 ORDER BY 2 DESC'
    BEGIN TRANSACTION
     --PRINT CAST(@OuterTableNumber AS VARCHAR(5))
     --PRINT CAST(@InnerTableNumber AS VARCHAR(5))
     PRINT (@SQL)
     SET NOCOUNT OFF
     EXEC (@SQL)
     --RETURN
    COMMIT TRANSACTION
    END
  FETCH NEXT FROM INNERCURSORNAME 
  INTO @VarTable2Schema, @VarTable2Name
  END
  CLOSE INNERCURSORNAME
  DEALLOCATE INNERCURSORNAME
  -- INNER CURSOR END
  END

FETCH NEXT FROM CURSORNAME 
INTO @VarTable1Schema, @VarTable1Name
END
CLOSE CURSORNAME
DEALLOCATE CURSORNAME

GO

Simulate Server Load

Loop to execute USP_RandomLoad multiple times to simulate a server load -

DECLARE  @CurrentAttempt INT
DECLARE  @TotalAttempts INT
                        
SET @TotalAttempts = 10
SET @CurrentAttempt = 1
                      
WHILE @CurrentAttempt <= @TotalAttempts
  BEGIN
    PRINT CHAR(13) + 'Run ' + CAST(@CurrentAttempt AS VARCHAR(5))
    EXECUTE USP_RandomLoad  
    SELECT @CurrentAttempt = @CurrentAttempt + 1
  END

Determine Server Startup DATETIME

One way to determine uptime (start time , restart time), i.e. when the server started up -

select create_date from sys.databases where name = 'tempdb'

SQL Profiler Permissions

Allowing non admin to run SQL Profiler >

GRANT ALTER TRACE TO [developer]

Filegroups : Where are my Indexes / Tables Stored?

Which filegroups are utilised by which database objects?
A Tsql script to help out...

SELECT
   fg.data_space_id
 , fg.name
 , OBJECT_NAME(p.object_id) AS [ObjectName]
 , p.index_id
    , dbf.name
    , dbf.physical_name
    , dbf.size*8/1024 AS [Size] -- Size of file 
    -- size is in number of 8KB pages. Multiply by 8 to get figure per extent (64KB),
    -- then divide by 1024 to get index size in MB.
 FROM sys.filegroups fg
 LEFT JOIN sys.database_files dbf
      ON fg.data_space_id = dbf.data_space_id
 LEFT JOIN sys.partitions p
      ON fg.data_space_id = p.partition_number
 WHERE (p.object_id>4096 OR p.object_id IS NULL)

Evil Tables without PRIMARY KEYS

Can't be replicated. Here's how to find them....

SQL 2005+

SELECT SCHEMA_NAME(schema_id) ,name
FROM sys.tables 
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY 1, 2

SQL 2000

SELECT c.name, a.name
FROM sysobjects a
INNER JOIN sysusers c ON a.uid = c.uid
LEFT OUTER JOIN (SELECT id FROM sysindexes WHERE (status & 2048)<>0 )b on a.id = b.id
WHERE xtype = 'U'
AND b.id IS NULL

Enabling Database Trust

"Msg 916, Level 14, State 1, Procedure usp_LatestJumpData, Line 4
The server principal "sql_login" is not able to access the database "Source" under the current security context."

To allow procedures in one database to access data in another >

ALTER DATABASE Source SET TRUSTWORTHY ON;
ALTER DATABASE Reporting SET TRUSTWORTHY ON;

Tools : Active Directory Explorer v1.01

Active Directory Explorer v1.01

http://www.microsoft.com/technet/sysinternals/Networking/AdExplorer.mspx

This is a really useful tool for viewing AD objects & properties.
Extremely useful when you cant get onto a Member Server or Domain Controller to use the AD administration tools.