Tuesday 30 December 2008

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.

    Tuesday 23 December 2008

    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]
    

    Sunday 21 December 2008

    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

    Saturday 20 December 2008

    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.

    Tuesday 16 December 2008

    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!

    Saturday 13 December 2008

    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...

    Monday 8 December 2008

    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.

    Sunday 7 December 2008

    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.

    Friday 5 December 2008

    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.

    Thursday 4 December 2008

    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

    Wednesday 3 December 2008

    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

    Monday 1 December 2008

    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