Saturday 20 November 2010

Updating Statistics Manually

To update statistics manually -

exec sp_updatestats

Without parameters this updates all statistics in current database.

Run in all databases like this -

exec sp_MSForeachdb 'use [?];exec sp_updatestats'

Indexed views

sp_updatestats does not update indexed view stats

Friday 19 November 2010

SQL login passwords and Pwdcompare

I'll spare the details but after a a little investigation, the HASHBYTES function cannot be used to generate a SQL login password. There are 2 undocumented functions pwdencrypt and pwdcompare that Management Studio uses for this.

If you were really interested in how to form the passwords, then the login migration script here will give more insight.

Link : How to transfer the logins and the passwords between instances of SQL Server 2005/8

The use of pwdcompare can be demonstrated as follows -

If I was daft enough to use my cat's name as a password (I'm not), the following query would return the username I had used the password with.

select name from sys.syslogins where pwdcompare('coco', password) = 1


Links:
cannot engineer sql password hash using HASHBYTES, have to use the undocumented stored procedure 'pwdencrypt'
SQL Server undocumented password hashing builtins: pwdcompare and pwdencrypt

Thursday 18 November 2010

Bookmark : WhoIsActive 10.0 / WhoIsActiveUI

Adam Machanic’s FREE WhoIsActive script has reached version 10.0

Download : WhoIsActive 10.0

and SchemaSolutions have produced a FREE SSMS plugin for it, called WhoIsActiveUI .

Download : WhoIsActiveUI

r

Friday 12 November 2010

Bookmark : Project Lucy

Quest Software have launched Project Lucy to analyse SQL Profiler trace (.trc) files.

Get their opinion on your servers for free at https://www.projectlucy.com/

Tuesday 9 November 2010

Bookmark : Naming standardization scripts

Michael Søndergaard has written some excellent Naming standardization scripts that he has published on his site.

They cover the naming of Indexes , Check Constraints, Default Constraints and Foreign Keys and can potentially save you a lot of time.
This of course is only if you can agree a Naming convention with your colleagues...

r

dbautils.spFixIndexNaming
dbautils.spFixColumnCheckNaming
dbautils.spFixColumnDefaultNaming
dbautils.spFixForeignKeyNaming

Wednesday 3 November 2010

Missing IDs in an integer sequence (the gap problem)

I encountered a missing integer ID problem today and rather than reinventing the wheel found the following script to deal with it.

Finding (all the) gaps in an identity column (or any integer based column for that matter) using Sql 2005

I have added OPTION (MAXRECURSION 0) to the end of the SELECT statement so that the CTE can be called as many times as necessary.

declare @i int;
 SELECT @i = MAX(pkid) FROM t1;
 WITH tmp (gapId) AS (
   SELECT DISTINCT a.pkid + 1
   FROM t1 a
   WHERE NOT EXISTS( SELECT * FROM t1 b
        WHERE b.pkid  = a.pkid + 1)
   AND a.pkid < @i
   UNION ALL
   SELECT a.gapId + 1
   FROM tmp a
   WHERE NOT EXISTS( SELECT * FROM t1 b
        WHERE b.pkid  = a.gapId + 1)
   AND a.gapId < @i
 )
 SELECT gapId
 FROM tmp
 ORDER BY gapId
OPTION (MAXRECURSION 0) ;
A good article on other sequence generation scripts is Creating a Number (Sequentially incrementing values) table in T-SQL

Tuesday 2 November 2010

Monday 1 November 2010

Tool : CPU-Z

I just read a post of Brent Ozar’s about the SQL Server installation at Stackoverflow.com and how the power-saving feature of the Nehalem processor was causing some fun.

Anyway, he points to an excellent FREE tool, CPU-Z which is new to me.
As Brent's post details, compare the 'specification' of the processor with the 'core speed' it is achieving.

Simples...

Download CPU-Z : http://www.cpuid.com/