SQL 101 : Tempdb

This post explains exactly what tempdb is responsible for -

Iain Kick - Not another tempdb post

And what to do to tune it, namely -

1) RAID level (& separate version)
2) Instant File Initialization
3) Trace Flag T1118
4) Pre Size tempdb
5) Split tempdb per processor core

Rename Filegroup

 How to change the logical name of a filegroup...

ALTER DATABASE databasename MODIFY FILEGROUP [OldName] NAME = [NewName]

simples ...

Moving Tables / Indexes across filegroups

How to move a table / index to a new filegroup
This can be a costly operation in terms of time and I/O

1) Create the filegroup

ALTER DATABASE myDatabase ADD FILEGROUP NewFileGroup
GO

ALTER DATABASE myDatabase
ADD FILE
( NAME = N'NewFileGroupData'
, FILENAME = 'D:\Data\NewFileGroupData.mdf'
, SIZE = 5000MB
, FILEGROWTH = 10%)
TO FILEGROUP NewFileGroup
GO

2A) If the table has a primary key or unique constraint, drop and recreate the constraint ...

USE myDatabase  
GO  
   
ALTER TABLE my.table DROP CONSTRAINT PK_Move WITH (MAXDOP =1,MOVE TO NewFileGroup)
GO  
   
ALTER TABLE my.table ADD CONSTRAINT PK_Move PRIMARY KEY(id) WITH(MAXDOP =1)  
GO 

2B) For a clustered / non-clustered index (outside of a constraint), recreate the index
using the CREATE INDEX statement, with DROP_EXISTING = ON

USE myDatabase  
GO  
CREATE NONCLUSTERED INDEX [ix_movethisindex] ON [my].[table] 
(
 [column1] ASC,
 [column2] ASC,
 [column3] ASC
)WITH (DROP_EXISTING = ON, ONLINE = ON, DATA_COMPRESSION = PAGE) ON [NewFileGroup]
GO

Note : I use;

• ONLINE = ON for an ONLINE index build (Enterprise, Developer, and Evaluation editions only)
• DATA_COMPRESSION = PAGE (Am on SQL 2008 Enterprise and am utilising compression functionality)

2C) To move heaps (tables with no clustered indexes) across filegroups, create a clustered index on the new filegroup, and then remove it again. This has the downside of temporarily ordering the table.

USE myDatabase  
GO  

CREATE CLUSTERED INDEX [ix_temp] ON [my].[table] 
(
 ID ASC
)WITH ( ONLINE = OFF, DATA_COMPRESSION = PAGE) ON [NewFileGroup]
GO

DROP INDEX [ix_temp] ON [my].[table] 

Sysinternals : Process Monitor

Off-topic, but I wanted to share what a great tool Process Monitor is.

The screen shot below shows me debugging the installer of the latest iTunes update which refused to go onto my laptop.

Sysinternals Suite : Process Monitor

PHP / FreeTDS / ANSI NULLS

Came across a problem today where default database settings for ANSI NULLS came into play.

Developers running queries from the application recieved application errors yet the same queries in management studio ran fine.

Have set database defaults according to the screenshot below now (the PHP app communicating via FreeTDS didnt set any connection properties).

VAMT : "SKU not Supported"

In VAMT (Volume Activation Management Tool) i'm getting the message "SKU not Supported" for new Windows 2008 R2 VMs.

If you get this, you need to upgrade VAMT to version 2.0 (I was using 1.1) >

Download : Volume Activation Management Tool 2.0

VAMT 2.0 has a new look and is now a MMC snap-in too.

Bookmark : How to build and maintain a tiered WSUS infrastructure

Could have done with this a couple of months back...

How to build and maintain a tiered WSUS infrastructure

SQL Service Account Rights

Permissions assigned for my network Service Accounts -

Group Policy to Enable Instant File Initialization

If you run SQL Server using Network Service accounts, you'll need the following location in Group Policy to allow Instant File Initialization.

Grant 'Perform Volume Maintainence Tasks' to the SQL Server service account as shown below -








http://www.sqlskills.com/blogs/Kimberly/post/Instant-Initialization-What-Why-and-How.aspx

SQL 2008 : Management Data Warehouse (MDW) Walkthrough

Delete SQL backup files over X days old.

Put this in a batch file and schedule it!

FORFILES /p C:\!DB_BACKUP_TEMP /s /m *.BAK /d -7 /c “CMD /C del /Q @FILE”

Careful copying the above command, the double quotes mess it up! copy into notepad, retype the double quotes, then it will be fine!

Note the -7 for 7 days.

http://thebackroomtech.com/2007/12/07/howto-automatically-remove-files-older-than-%e2%80%98x%e2%80%99-days-part-ii/

I/O Delays

" SQL Server has encountered n occurrence(s) of I/O requests taking longer than 15 seconds to complete on file d:\path\datafile.mdf "

This is SQL's way of saying the I/O Subsystem is not coping with the data throughput.

" When you see this message the first action should still be to have a look at the physical disk counters in sysmon to ensure that the disks are servicing IOs in a reasonable period of time. If those appear to fine then start looking at what filter drivers might be installed on your system, and if there are any known issues with them, or disable them if you don’t need them. "

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx

SQL 2005 : SP3

SP3 for SQL 2005 is out.

Beware if you've already applied CU10 / CU11 though, as SP3 is based on CU9!
You'll need to run CU1 for SQL 2005 SP3 therefore!

System Center Virtual Machine Manager 2008

RTM is released. Now just have to wait for the MSDN discs :)

http://msdnrss.thecoderblogs.com/2008/10/21/system-center-virtual-machine-manager-2008-has-rtmed/

http://www.microsoft.com/systemcenter/scvmm/downloadbeta.mspx

SysPrep

Generates a new random SID (security ID), unique to the image / VM.

Start > Run > Sysprep [Enter]


If you forget to do this in preparing your VMs, you'll need NewSid.

Disk System Pressure

Woah!!!!! This sql instance is really struggling with I/O >


The steps I took to rectify this were >
1) File Defragmentation (well I moved the files off and back on to the disk as it was quicker)
2) Data Defragmentation (implemented a deframentation job & scheduled it)
3) Moved tempdb to a separate physical disk

Performance Impact of Enabling Page Checksum and Default Trace

Whilst hunting options to squeeze every last drop out of a life out of an SQL instance, i toyed with the idea of disabling the database 'Page Checksum' and 'Default Trace' options.

The full article is linked below, but the upshot is that there is almost negligable performance decrease.

http://sqlblog.com/blogs/linchi_shea/archive/2007/01/16/performance-impact-of-enabling-page-checksum-and-default-trace.aspx

Windows Uptime

Go to a command (dos) prompt -
Type 'net statistics server'

Look for the statistics since line - 

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\user>net statistics server
Server Statistics for \\MYPC-123


Statistics since 7/30/2008 10:44 AM


Sessions accepted                  1
Sessions timed-out                 0
Sessions errored-out               0

Kilobytes sent                     0
Kilobytes received                 0

Mean response time (msec)          0

System errors                      0
Permission violations              0
Password violations                0

Files accessed                     0
Communication devices accessed     0
Print jobs spooled                 0

Times buffers exhausted

  Big buffers                      0
  Request buffers                  0

The command completed successfully.


C:\Documents and Settings\user>


Link : How to find windows uptime?

10 Ultimate Rules for Effective System Administration

1. Keep it simple.

2. Backup regularly

3. Test your backup regularly

4. Proactive Monitoring

5. Document Everything

6. Plan and Execute it well.

7. Use Command Line more than GUI

8. Automate repetitive tasks

9. Support your users and developers

10. Keep learning and have fun.


The excellent, full post on the subject is here >

http://www.cyberciti.biz/tips/10-ultimate-rules-for-effective-system-administration.html

"Log on as a Service" Right (adding locally)

1) Launch the 'local Security Policy' console

Start Menu > Administrative Tools > Local Security Policy.


2) Expand the tree as follows >

Security Settings > Local Policies > User Rights Assignment

Double click 'Log on as a service' & add service accounts to give them permission.