2008

4 months ago, I changed Job, seeking more a role with more sql development rather than focusing on administration. The dba role is interpreted very differently by smaller companies and as I wanted a more challenging environment I moved on.

Although real development project work is only in the planning stage, I've achieved a lot since September -

Fixing the obvious -

1. Implementing Backups (that's right, there were none!)
2. Addressing Index Fragmentation with scripts.
3. Fixing massive file fragmentation of live databases (damn that autogrowth setting)

Virtualisation -

1. Getting to grips with virtualisation in a production environment.
2. Virtualised SQL Server 2005 on Microsoft’s Hyper-V platform.

Networking -

1. Building my own 13 VM network for the datawarehouse on 4 physical hosts.
2. Building a Windows 2008 domain. 
3. Fun with Group Policy (controlling RDP, Windows Firewall, Windows Updates).
4. Implementing VAMT for Product Activation, Windows Updates and firewalls.

Development -

1. Rewriting worst performing front end sql queries in parameterised stored procedures.
2. Improving indexes using the Database Tuning Advisor.
3. Reviewed existing systems.

Existing systems -

The core product database has suffered from being developed quickly by 1 person.
It's major faults are >

1. Passwords stored in PHP code. - Not an easily configurable location, code not portable.
2. Application code granted excess permissions - This is due to TRUNCATE present in code.
3. Import and Reporting systems sharing the same server.
   Statistics show disk activity is 99% write, 1% read.
4. Cross db ownership - Live code accesses both application and import database using 3 part object naming i.e. db_name.schema_name.object_name.
5. Nested views! - Views that are built from views, that are built from views etc...  (Inefficient , difficult to debug, difficult to optimize).
6. Adhoc queries - SELECT statements embedded in php code where stored procedures would increase simplicity (for web developer), ensure query parameterization (for performance) and increase security (reduce chances of an injection attack),
7. Poor table design - Many tables have lots of columns. Databases badly need some normalization.

SQL 2005 : Index Performance

Index Types from Fastest > Slowest

[1] Non-Clustered Covering with Included non-key columns
[2] Non-Clustered Covering
[3] Clustered
[4] Non Clustered Non Covering
[5] No Index

therefore, for a Non-Clustered Index with Included non-Key columns >

CREATE INDEX nci_IndexName
ON TABLE (keycolumn_1,keycolumn_2)
INCLUDE (nonkeycolumn_1,nonkeycolumn_2)

SQL Query : Logical Processing Phases

(8) SELECT (9) DISTINCT (11) TOP [column list]
(1) FROM [table_1]
(3) [join_type] JOIN [table_2]
(2) ON [condition]
(4) WHERE [condition]
(5) GROUP BY [column list]
(6) WITH [CUBE | ROLLUP]
(7) HAVING [condition]
(10) ORDER BY [column list]

Agile

Interesting posts on Agile Development...

http://www.techcrunchit.com/2008/12/21/will-this-economy-finally-push-the-toyota-way-into-software-development/

http://jamesshore.com/Blog/The-Decline-and-Fall-of-Agile.html

Using Missing Indexes DMVs to generate index suggestions

I came across this today.
It uses the missing_indexes dmvs to recommend where indexes could be added.
Have modified it to include the table schema.

SELECT     'CREATE NONCLUSTERED INDEX NewNameHere ON ' + sys.schemas.name + '.' + sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL 
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL 
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns, 
mid.included_columns
FROM         sys.dm_db_missing_index_group_stats AS migs 
INNER JOIN   sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
INNER JOIN   sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle 
INNER JOIN   sys.objects WITH (nolock) ON mid.object_id = sys.objects.object_id
INNER JOIN   sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id 
WHERE     (migs.group_handle IN
(SELECT     TOP 100 PERCENT group_handle
FROM          sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND sys.objects.type = 'U'

Original Piece


MSDN : Using Missing Index Information to Write CREATE INDEX Statements


Brian Knight's blog on the Missing Index DMV

Log file reuse Problem

" Date 20/12/2008 17:30:19
Log SQL Server (Current - 20/12/2009 00:00:00)

Source spid62

Message 'ImportSystem' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"

Simple one this, the error tells you exactly what to do...

select log_reuse_wait_desc, *  from sys.databases

The log file cannot be reused due to an Active Transaction.

CHECKSUM Explained

good video tutorial over on SQLServerCentral

Forcing Query Plan Choice with USE PLAN hint

USE PLAN can be used to bypass SQL's choice of query plan.
Microsoft white paper here...
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

SQL 2008 : IFTS - Integrated Full Text Search

Full Text search is no longer an external feature.

http://sqlblogcasts.com/blogs/simons/archive/2008/02/19/SQL-Server-2008---iFTS-Introduction.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!

Windows 2008 RDP Session gets stuck 'Preparing Desktop'

Couldn't find the answer to this anywhere.
It turned out that installing applications whilst 'User Account Control' was enabled had interfered with the desktop profiles.
Have temporarily disabled UAC to get round the situation. Not ideal, but theres a solution for anyone else encountering the problem...

SSIS : Package Execution

Command line package execution -

DTEXEC.EXE /F "D:\SSISDeployment\SSISPackage.dtsx"

BIDs -
F5 - Run Package in OS (without screen display)

Update Group Policy immediately

C:\> GPUPDATE /FORCE

Run this from the command prompt on client machines to overide the default GP refresh period i.e. have Group Policy applied straight away.

Windows 2008 Firewall Rules for SQL Server (via Group Policy)

Configuring Windows 2008 Firewall for SQL Server

1) Launch Group Policy and navigate to >
Computer Configuration > Policies > Windows Settings > Security Settings > Windows Firewall with Advanced Security
2) Right Click on Inbound rules , click 'New Rule'
3) Select 'Port Rule', Click 'Next'
4) Select 'TCP' and enter specific ports ' 1433, 1434 ' (comma separated). Click 'Next'.
5) Select 'Allow the connection' Click 'Next'.
6) Use tick boxes to restrict the view via profile. (Domain/Private/Public). Click 'Next'
7) Provide a name and optionally a description for Rule. Click 'Finish'.

Repeat the above for Outbound Rule too.
You may need to open other ports too, see SQL Server Default Ports

NOTE : Remember to run GPUPDATE /FORCE on client machines to overide the default GP refresh period.

Group Policy : Allowing RDP Access

1) Add Users in Active Directory to a common group e.g ; 'RDP Access Group'

2) In group policy (start > run >gpedit.msc) navigate to >
Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment.

3) Add 'RDP Access Group' to the 'Allow log on through Terminal Services group' setting.

4) Add 'RDP Access Group' to the 'Allow log on locally' policy too.

5) Run GPUPDATE to force the policy to take effect to test.

SQL DateTime > Unix Timestamp

I was blissfully ignorant of UNIX timestamps until a colleague needed to fetch datetime data from one of my servers.

Basically, its just the offset since a given epoch (1st Jan 1970) in seconds.

Returning a Unix timestamp from a SQL Date >

SELECT DATEDIFF(s, '19700101', GETDATE()) 

Returning a SQL Date from a Unix timestamp >

SELECT DATEADD(s,1228348800, '19700101') 

Unix timestamp

Moving deployed SSIS packages into folders

Moving deployed SSIS packages into folders ...

from command prompt >

dtutil /SQL "LoadDataPackage" /SourceServer SQL-LIVE-01 /MOVE SQL;"/PackageFolder/LoadDataPackage" /DestServer SQL-LIVE-01

SQL 2008 CU2

This one slipped in without me noticing!

CU2 for SQL 2008 is out...

http://support.microsoft.com/default.aspx/kb/958186/en-us

SQL Restart without a restart...

I blogged last year on clearing sql servers' various memory caches, useful for testing code.
I read this today and it turns out that what i'm achieving here is restarting the server without restarting (if you follow).

Those commands again ...

-- To clear the procedure cache
DBCC FREEPROCCACHE

-- To clear the data cache
DBCC DROPCLEANBUFFERS