Wandenkolk & SQL Server

SQL server Cluster do and don´ts

2011-01-06T13:34:00.001-03:00

It worth reading, but still this is not what i´m looking for

http://support.microsoft.com/kb/254321/en-us

Happy New Year!

2010-12-30T08:34:00.001-03:00

I know I know… this year this blog had few updates. Indeed. Still, I might put on the table a lot of arguments. At my previous company I actually had a bit more of time to write down my ideas and discovery, here at my new job I have lesser hours to work on it. Besides, with a few family issues, it was a bit more difficult to get focused on it.

But here comes 2011, and I have big plans, get back to teaching classes, taking control of my ADHD, and writing new articles on my Blog.

For all of you I convey my best wishes of a brilliant and sucessful new year!

Cheers!

Detecting High CPU Usage

2010-12-09T08:47:00.001-03:00

SELECT TOP 50
        qs.total_worker_time/qs.execution_count as [Avg CPU Time],
        SUBSTRING(qt.text,qs.statement_start_offset/2,
            (case when qs.statement_end_offset = -1
            then len(convert(nvarchar(max), qt.text)) * 2
            else qs.statement_end_offset end -qs.statement_start_offset)/2)
        as query_text,
        qt.dbid, dbname=db_name(qt.dbid),
        qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
        [Avg CPU Time] DESC

Moving System databases walkthrough

2010-02-05T12:06:00.001-03:00

Moving System Databases is a delicate process, get rid of everyone around you, specially your manager. :)

The Basics

1. First verify where are the files, and to where you got to move them.

2. Make sure to create on the destination the proper folders for sql server data.

3. Ensure that the service account has ntfs permissions over those folders. Permissions are always a problem, if you forget it, sql server cannot put the databases online once it has no access to the files… remember that.

First the easy part

Moving the tempdb, Model, and Msdb

--use master
--Select name, filename from sysaltfiles where name not like 'mast%' -- sysaltfiles shows all the data files for all the databases… very handy

USE MASTER

ALTER DATABASE tempdb modify file (NAME='tempdev', FILENAME='newlocation\Data\tempdb.mdf')
ALTER DATABASE tempdb modify file (NAME='templog', FILENAME='newlocation\Data\templog.ldf')

ALTER DATABASE model modify file (NAME='modeldev', FILENAME='newlocation\data\model.mdf')
ALTER DATABASE model modify file (NAME='modellog', FILENAME='newlocation\data\modellog.ldf')

ALTER DATABASE MSDB modify file (NAME='MSDBData', FILENAME='newlocation\data\msdbdata.mdf')
ALTER DATABASE MSDB modify file(NAME='MSDBLog', FILENAME='newlocation\data\msdblog.ldf')

Stop the sql server

Move the actual datafiles to the new locations, they won´t move for themselves.

Say your prayers and start the Sql server

--- Uhhhh, SQL Server started, and all databases are online…good for you. now comes the funny part. Move the master

-- Move the master, by changing the master location on configuration manager, if in cluster do that on both nodes

look for service properties, advanced tab, startup parameters property

-dnewlocation\Data\master.mdf;-enewlocation\log\ERRORLOG;-lnewlocation\Data\mastlog.ldf

the –d parameter is the location of master.mdf file

the –l parameter is the location of master log file

the –e parameter is the location of the errorlog (very very important)

Newlocation is the PATH where the files will be located after the moving, please don´t write newlocation it won´t work… you got to write the full path where the file will be, and for god sake, make sure that the path exists and is spelled correctly

Remember that all parameters are on the same line and the parameter and the value are all toghether (no blanks). if you write it wrong sql server won´t come online. Hopefully you can change it after

If you are working in clustered environment, you got to change the startup parameter on all nodes where the instance belongs to.

Now is time to change the resource database of location, only applicable on SQL Server 2005. On SQL 2008 the system resrouce database is located on sqlroot\binn and you cannot change it.

What should i do them?? Nothing, leave it on the installation drive…

1. start-up the instance in master recovery mode, on command prompt fun the following

net start MSSQLSERVER /f /T3608

net start MSSQL$INSTANCENAME /f /T3608

And then connect using the sqlcmd.exe, and run the following to move the system resource db

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'V:\MSSQL2000\MSSQL$SQLC\Data\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'V:\MSSQL2000\MSSQL$SQLC\Data\mssqlsystemresource.ldf');
GO

-- Stop the instance, say your prayers and start it again.

Sometimes, depending on the operation you gonna do you still have to change the path where sql server agent put its logs

USE msdb
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'V:\MSSQL2000\MSSQL$SQLC\LOG\SQLAGENT.OUT' -- the new path obviously
GO

Now, you test if everything is running fine and you finished this delicate operation. Remember to failover the cluster group where sql server is running in order to make sure that everything is fine ON BOTH NODES. Collect some test evidence that you tested to make that everything was running ok, and you have to prove it.

IF YOU ARE NOT SURE OF THESE PROCEDURES. DON´T EXECUTE THEM IN PRODUCTION, create a test environment, simple sql server install on a VM or even in your own computer, and test the procedures.

Check the MSDN article with the procecedures above. http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

This documentation is provided as is, and you know what it means. :)

Have fun!!!

Transparent Data Encryption on SQL 2008 (TDE)

2010-01-22T10:55:00.001-03:00

Actually over the years sql server has improved a lot in terms of security. In the beginning, a long time ago, the industry didn´t have much concerns about security, even because this kind of implementation requires cost of development, implies in a more rigid development policies (to develop minimizing the surface area of attack), and the types of attack were not as severe as they are today (and today they imply in loosing money).

Over the last two versions of SQL Server (2005 and 2005) Microsoft made a big effor in making the product more secure. The reasons are simple. If they want SQL Server grow in more mission critical applications, the system must be protected from attacks, and more critical the application and the data, SQL Server must be more secure in terms of data access.

That´s why the term “secure by default” became a clichè in many Microsoft presentations regarding SQL Server.

One of the features on SQL 2008 related to security that at the same time is simple to implement and important for highly confidential database is Transparent Data Encryption. It covers two different aspects.

One, if you manage to detach a SQL Server database you might virtually attach it back in any other SQL Server instance. In this case someone with access to the system can steal the information and bypass security and auditing by simply creating a copy of the database. Which is a problem in terms of confidentiality.

Other is less common but the data inside the database could be exposed by some disk editing tool, which can check the database file internally, once the database file has no level of encryption it could be a risk, someone sniffing your disk searching for some information.

Transparent Data Encryption addressses these problems by creating all data inside the database encrypted. The data inside the database file is coded, and at the same time you cannot attach the database on other server, without having installed the key and certificatesd to decrypt the database

The process consists in creating a Master key, and a certificate. Later using this certificate to encrypt the database. The master key, should have a password which must be strong in order to be more difficult to break by brute force, for example:

/*
Script enable Transparent Data Encryption on AdventureWorks database
*/
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Fum0V0rt3m0@$ql';
go
CREATE CERTIFICATE CertDecriptacao_advwrks WITH SUBJECT = 'Certificvado de decriptação para AdventureWorks'
go
USE AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE CertDecriptacao_advwrks
GO
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON
GO

A good way to work with it is to make some tests in an isolated environment first and them pass it to production in specific databases. It is pretty clear that the encryption process impacts on the overall performance by creating some cpu overhead.

So … have fun!!

4NF 5NF and 6NF

2010-01-13T15:51:00.001-03:00

Follows a post i wrote on Brazilian Technet, later i will translate it to english.

4FN Quarta forma normal
“Uma tabela está na 4FN, se e somente se, estiver na 3FN e não existirem dependências multivaloradas”
Pode-se ver como a transformação de algo assim
Supondo que cada paciente pode ter mais de um plano de saúde, e pode fazer vários exames
Paciente   Plano      Exame
Carlos      SuaVida       Hemograma
Carlos      SL               Hemograma
Carlos      SuaVida       Urina
Carlos      SL               Hemograma

A idéia da 4FN seria dividir a informação em duas tabelas distintas
Uma com a Pedido do exame
e outra com a infromação de quais os planos q o paciente suporta, isso evitaria a redundência dessa informação em cada solicitação.
Paciente    Exame
Carlos       Hemograma
Carlos       Urina
Paciente PLano
Carlos    SuaVIda
Carlos    SL

A quinta forma é chamada de Forma de Projeção e União (Project/Join Normal Form)
Considere o exemplo
Vendedor   Marca       Tipo de Produto
Carlos        MArba      Aspirador de pó
Carlos        MArba      Torradeira
João          Tabajara   Geladeira
João          Tabajara   Torradeira
Arthur        Capivara   Aspirador de Pó
Arthur        Capivara   Tanquinho
Arthur        Capivara   Geladeira

Agora, suponha a seguinte regra: um vendedor tem um determinado conjunto de marcas e certo tipo de produtos na sua carteira de vendas.
Então poderiamos ter a tabela dividida da seguinte forma
Vendedor   Tipo de Produto
Carlos        Aspirador de pó
Carlos        Torradeira
João           Geladeira
João           Aspirador de Pó
Arthur        Tanquinho
Arthur        Geladeira

Numa outra tabela as marcas q o vendedor vende

Vendedor    Marca
Carlos        Marba
Joao           Tabajara
Arthur         Capivara

E logicamente numa outra tabela a lista de Produtos e a qual marca ele pertence

Produto               MArca
Aspirador de Pó   Marba
Aspirador de Pó   Tabajara
Torradeira           Marba
Torradeira           Tabajara
Geladeira            Tabajara
Geladeira            Capivara
Tanquinho           Capivara

A 6FN ela diz que os bancos de dados são uma extensão da álgebra relacional, existe um livro de Crhistopher J. Date e outros sobre Bancos de Dados temporais. Esse estudo pelo que eu vi é bem teórico, tem alguma aplicações práticas em cenários de DataWarehouse, existe alguma literatura do Date, e de um outro autor chamado Zimanyi, Agregações Temporais.
Espero que ajude, em poucas linhas é complicado definir, recomendaria procurar alguma literatura especializada em modelagem de dados. Outra coisa, lembre das implicações práticas. A normalização excessiva provoca um aumento no número de tabelas uma vez que se cria conjuntos não redundantes de dados, porém mais conjuntos. Em alguns cenários como DataWarehouses, as regras de normalização são desrespeitadas, em função da modelagem de DW trabalhar com preceitos de modelagem dimensional, uma vez que a informação é entendida como um conjunto de fatos de negócio rodeados por dimensões desses fatos.

Android

2010-01-04T20:48:00.001-03:00

Apart from the Sql server usual topics, i´d like to register that i´m really impressed with the development of android plattform. you can find application for every task you want on the market, and they have a live community writing apps and earning money with that. It´s amazing the number of phones being shipped with the android os inside.. and what makes me more marvelled is how easy the operating system is to deal with.

I would say that even for a larger device, with this interface and plattform, android can grow to bigger devices in a near future, like ebook readers (let´s say nook from B&N) through tablet devices.

Android can represent the achievement that linux could not offer to the end user. Instead of fighting against Microsoft like linux and trying to create something better, Android is making it´s own way, which is awesome.

Microsoft a few years ago, was on the top about operating system technology, but somehow lost the track. I´m not an IPhone user neither an apple fan boy, but i have to agree that usability of the Iphone made everyone think about moving applications and making it more usable on the phone, and even more.. making its own phones with a better interface. Android could achieve that by creating an operating system open to multiple hardware platforms and using open standards all over it.

Google´s next step is Chrome OS, which can´t cause a huge impact on Windows empire… but again the innovation is on their side.More and more, the applications will become web based… and being such way, doesn´t matter if you are running Windows, Linux, Android, Leopard, Snow Leopard, Mountain Lion or anything… what do matter is what services can i bring to my browser, because every application would be a webpage.

More and more the strategies of those big companies would involve services, like google … like OVI from Nokia, like buying content with Itunes and so many others.

Big computing clouds will surround us, with content stored somewhere, published by someone, hosted by some machine in some datacenter around the world… but still it doesn´t matter, what matter is the content you receive, the physical location is not relevant because all the intelligence is stored somewhere, and your browser has just to render it before your eyes.

What we do, regarding SQL Server it is an important part of this future, because no matter what you do, the data must be stored somewhere, and must be accesible and secure. Must be quick to access, and must be reliable… the applications must work, webservices must be exposed. Services must be listening queues in order to process transactions, all this work is paramount to this future be viable.

When i look a small device carring a smart operating system on that i see i good idea, a lot of people working on it and a huge infrastructured to make it work.

Thanks

Congrats Android team.

About SQL Server Performance Parameters

2010-01-04T19:11:00.001-03:00

Logical Disk: Disk Transfers/sec. This counter provides the overall throughput on the specific disk. Use this counter to monitor growth trends and forecast appropriately.

Logical Disk: Disk Read Bytes/sec & Disk Write Bytes/sec. This counter provides a measure of the total bandwidth for a particular disk.

Logical Disk: Average Disk sec/Read (Read Latency). This counter indicates the time it takes the disk to retrieve data. On well-tuned I/O subsystems, ideal values are 1-5 ms for logs (ideally 1 ms on a cached array), and 4-20 ms for data (ideally below 10 ms). Higher latencies can occur in peak times, but if high values are occurring regularly, investigate the cause.

Logical Disk: Average Disk sec/Write (Write Latency). This counter indicates the time it takes the disk to write the data. On well-tuned I/O subsystems, ideal values would be 1-5 ms for log (ideally 1 ms on a cached array), and 4-20 ms for data (ideally below 10 ms). Higher latencies can occur in peak times, but if high values are systematically occurring, investigate the cause.

Logical Disk: Average Disk Byte/Read. This counter indicates the size of I/Os being read. This value may affect disk latency and larger I/Os may result in slightly higher latency. When used to monitor SQL Server, this tells you the average size of the I/Os SQL Server is issuing.

Logical Disk: Average Disk Byte/Write. This counter indicates the size of I/Os being written. This value may affect disk latency and larger I/Os may result in slightly higher latency. When used to monitor SQL Server, this will tell you the average size of the I/Os SQL Server is issuing.

Physical Disk: % Disk Time: DataDrive. Monitor this counter to ensure that it remains below two times the number of disks.

Logical Disk: Current Disk Queue Length. For this counter, lower values are better. Values above 20 may indicate a bottleneck in the request waiting to be served by the disk, and should be investigated. Bottlenecks can create a backlog that may spread beyond the current server accessing the disk and result in long wait times for end users. Possible solutions to a bottleneck may be to add more disks to the RAID array, replace with faster disks, or move some of the data to other disks.

Logical Disk: Average Disk Queue Length. This counter indicates the average number of outstanding I/O requests. The general rule is that you should be at two or fewer outstanding I/O requests per spindle, but this may be difficult to measure due to storage virtualization, differences in RAID levels between configurations. Look for higher than average disk queue lengths in combination with higher than average disk latencies. This combination could indicate that the storage array cache is being over utilized or that spindle sharing with other applications is affecting performance.

Logical Disk: Average Disk Reads/Sec and Logical Disk: Average Disk Write/Sec. These counters indicate the rate of read and write operations on the disk. Monitor these counters to ensure that they remain below 85 percent of the disk capacity. Disk access time increases exponentially if reads or writes are more than 85 percent of disk capacity. To determine the specific I/O capacity for your hardware, refer to the vendor documentation, or use the SQLIO disk subsystem benchmark tool to calculate it. For more information, see SQLIO Disk Subsystem Benchmark Tool (http://go.microsoft.com/fwlink/?LinkID=105586&clcid=0x409).

When you are using RAID configurations with the Logical Disk: Average Disk Reads/Sec or Logical Disk: Average Disk Write/Sec counters, use the formulas listed in the following table to determine the rate of I/Os on the disk.

RAID level	Formula
RAID 0	I/Os per disk = (reads + writes) / number of disks
RAID 1	I/Os per disk = [reads + (2 * writes)] / 2
RAID 5	I/Os per disk = [reads + (4 * writes)] / number of disks
RAID 10	I/Os per disk = [reads + (2 * writes)] / number of disks

For example, if you have a RAID 1 system with two physical disks, and your counters are at the values shown in the following table:

Counter	Value
Average Disk Reads/Sec	80
Average Disk Write/Sec	70
Average Disk Queue Length	5

The I/O value per disk can be calculated as follows:

(80 + (2 * 70))/2 = 110

The disk queue length can be calculated as follows:

5/2 = 2.5

In this situation, you have a borderline I/O bottleneck.

Monitor disk latency and analyze trends. The amount of I/O and latency specific to SQL Server data files can be found by using the sys.dm_io_virtual_file_stats dynamic management view in SQL Server 2005. For more information, see sys.dm_io_virtual_file_stats (http://go.microsoft.com/fwlink/?LinkID=105587&clcid=0x409).

Credits for the PSS

Grouping Sets

2010-01-04T19:10:00.001-03:00

This is a feature on SQL 2008, it is meant to allow multiple groupings within the same statement.

It could make this

SELECT customer, NULL as year, SUM(sales) FROM T GROUP BY customer

UNION ALL

SELECT NULL as customer, year, SUM(sales) FROM T GROUP BY year

into this

SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))

(samples from MSDN website)

Sparse Columns and Sparse Property

2010-01-04T19:09:00.001-03:00

This could be interesting to save space in case we have a lot of records that could be filled or null.

Take a look at the books online excerpt

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

Sparse columns can be used with column sets and filtered indexes:

Column sets
INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. This column is called a column set. For more information about column sets, see Using Column Sets.
Filtered indexes
Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index. For more information, see Filtered Index Design Guidelines.

Sparse columns and filtered indexes enable applications, such as Windows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server 2008.

if you have the books online installed, take a look at the full article.

have fun

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/ea7ddb87-f50b-46b6-9f5a-acab222a2ede.htm

USE AdventureWorks
GO

CREATE TABLE DocumentStore
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL ) ;
GO

INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27)
GO

INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35')
GO

DBCC Statements

2010-01-04T19:08:00.001-03:00

This one is cool… i found it on the internet

Shows the list of DBCC commands. then
DBCC HELP(<command>)
Shows the syntax of an individual co

If you run DBCC HELP on all the commands you end up with this list:

DBCC activecursors [(spid)]

DBCC addextendedproc (function_name, dll_name) 

DBCC addinstance (objectname, instancename) 

DBCC adduserobject (name) 

DBCC auditevent (eventclass, eventsubclass, success, loginname
                                            , rolename, dbusername, loginid) 

DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag]) 

DBCC balancefactor (variance_percent) 

DBCC bufcount [(number_of_buffers)] 

DBCC buffer ( {'dbname' | dbid} [, objid [, number [, printopt={0|1|2} ]
                                  [, dirty | io | kept | rlock | ioerr | hashed ]]]) 
                                          
DBCC bytes ( startaddress, length ) 

DBCC cachestats 

DBCC callfulltext 

DBCC checkalloc [('database_name'[, NOINDEX | REPAIR])] 
                           [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]] 

DBCC checkcatalog [('database_name')] [WITH NO_INFOMSGS] 

DBCC checkconstraints [( 'tab_name' | tab_id | 'constraint_name' | constraint_id )] 
                                          [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS] 

DBCC checkdb [('database_name'[, NOINDEX | REPAIR])] 
                                  [WITH NO_INFOMSGS[, ALL_ERRORMSGS]
                                    [, PHYSICAL_ONLY][, ESTIMATEONLY][,DBCC TABLOCK] 

DBCC checkdbts (dbid, newTimestamp)] 

DBCC checkfilegroup [( [ {'filegroup_name' | filegroup_id} ] 
                                 [, NOINDEX] )] [WITH NO_INFOMSGS
                      [, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]] 

DBCC checkident ('table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] ) 

DBCC checkprimaryfile ( {'FileName'} [, opt={0|1|2|3} ]) 

DBCC checktable ('table_name'[, {NOINDEX | index_id | REPAIR}]) 
                                      [WITH NO_INFOMSGS[, ALL_ERRORMSGS]
                                      [, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]] 

DBCC cleantable ('database_name'|database_id, 'table_name'|table_id,[batch_size])

DBCC cacheprofile [( {actionid} [, bucketid]) 

DBCC clearspacecaches ('database_name'|database_id, 
                               'table_name'|table_id, 'index_name'|index_id) 

DBCC collectstats (on | off) 

DBCC concurrencyviolation (reset | display | startlog | stoplog) 

DBCC config 

DBCC cursorstats ([spid [,'clear']]) 

DBCC dbinfo [('dbname')] 

DBCC dbrecover (dbname [, IgnoreErrors]) 

DBCC dbreindex ('table_name' [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS] 

DBCC dbreindexall (db_name/db_id, type_bitmap) 

DBCC dbrepair ('dbname', DROPDB [, NOINIT]) 

DBCC dbtable [({'dbname' | dbid})] 

DBCC debugbreak 

DBCC deleteinstance (objectname, instancename) 

DBCC des [( {'dbname' | dbid} [, {'objname' | objid} ])] 

DBCC detachdb [( 'dbname' )] 

DBCC dropcleanbuffers 

DBCC dropextendedproc (function_name) 

DBCC dropuserobject ('object_name') 

DBCC dumptrigger ({'BREAK', {0 | 1}} | 'DISPLAY' | {'SET', exception_number} 
                                             | {'CLEAR', exception_number}) 

DBCC errorlog 

DBCC extentinfo [({'database_name'| dbid | 0} 
                 [,{'table_name' | table_id} [, {'index_name' | index_id | -1}]])] 

DBCC fileheader [( {'dbname' | dbid} [, fileid]) 

DBCC fixallocation [({'ADD' | 'REMOVE'}, 
                           {'PAGE' | 'SINGLEPAGE' | 'EXTENT' | 'MIXEDEXTENT'}
                                   , filenum, pagenum [, objectid, indid]) 

DBCC flush ('data' | 'log', dbid) 

DBCC flushprocindb (database) 

DBCC free dll_name (FREE) 

DBCC freeproccache 

dbcc freeze_io (db) 

dbcc getvalue (name) 

dbcc icecapquery ('dbname', stored_proc_name 
                              [, #_times_to_icecap  (-1 infinite, 0 turns off)])
     Use 'dbcc icecapquery (printlist)' to see list of SP's to profile.
     Use 'dbcc icecapquery (icecapall)' to profile all SP's.
		
dbcc incrementinstance (objectname, countername, instancename, value) 

dbcc ind ( { 'dbname' | dbid }, { 'objname' | objid }, { indid | 0 | -1 | -2 } )

DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid |indname})

DBCC inputbuffer (spid)

DBCC invalidate_textptr (textptr)

DBCC invalidate_textptr_objid (objid)

DBCC iotrace ( { 'dbname' | dbid | 0 | -1 }
                             , { fileid | 0 }, bufsize, [ { numIOs | -1 } 
                               [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] ) 

DBCC latch ( address [, 'owners'] [, 'stackdumps']) 

DBCC lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}] | 
                                          [{'STALLREPORTTHESHOLD', stallthreshold}]) 

DBCC lockobjectschema ('object_name') 

DBCC log ([dbid[,{0|1|2|3|4}[,['lsn','[0x]x:y:z']|['numrecs',num]|['xdesid','x:y']
                 |['extent','x:y']|['pageid','x:y']|['objid',{x,'y'}]|['logrecs',
                           {'lop'|op}...]|['output',x,['filename','x']]...]]])

DBCC loginfo [({'database_name' | dbid})] 

DBCC matview ({'PERSIST' | 'ENDPERSIST' | 'FREE' | 'USE' | 'ENDUSE'}) 

DBCC memobjlist [(memory object)] 

DBCC memorymap 

DBCC memorystatus 

DBCC memospy 

DBCC memusage ([IDS | NAMES], [Number of rows to output]) 

DBCC monitorevents ('sink' [, 'filter-expression']) 

DBCC newalloc - please use checkalloc instead 

DBCC no_textptr (table_id , max_inline) 

DBCC opentran [({'dbname'| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]] 

DBCC outputbuffer (spid) 

DBCC page ( {'dbname' | dbid}, filenum, pagenum 
                                 [, printopt={0|1|2|3} ][, cache={0|1} ]) 

DBCC perflog 

DBCC perfmon 

DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}
                                              , targetfile, targetpg, order={1|0}) 

DBCC pintable (database_id, table_id) 

DBCC procbuf [({'dbname' | dbid}[, {'objname' | objid}
                                          [, nbufs[, printopt = { 0 | 1 } ]]] )] 

DBCC proccache 

DBCC prtipage (dbid, objid, indexid [, [{{level, 0} 
                                    | {filenum, pagenum}}] [,printopt]]) 

DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )] 

DBCC readpage ({ dbid, 'dbname' }, fileid, pageid
                                 , formatstr [, printopt = { 0 | 1} ]) 

DBCC rebuild_log (dbname [, filename]) 

DBCC renamecolumn (object_name, old_name, new_name) 

DBCC resource 

DBCC row_lock (dbid, tableid, set) - Not Needed 

DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+) 

DBCC ruleon (  rulenum | rulestring } [, { rulenum | rulestring } ]+) 

DBCC setcpuweight (weight) 

DBCC setinstance (objectname, countername, instancename, value) 

DBCC setioweight (weight) 

DBCC show_statistics ('table_name', 'target_name') 

DBCC showcontig (table_id | table_name [, index_id | index_name] 
                         [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]]) 

DBCC showdbaffinity 

DBCC showfilestats [(file_num)] 

DBCC showoffrules 

DBCC showonrules 

DBCC showtableaffinity (table) 

DBCC showtext ('dbname', {textpointer | {fileid, pageid, slotid[,option]}})

DBCC showweights

DBCC shrinkdatabase ({dbid | 'dbname'}, [freespace_percentage 
                                            [, {NOTRUNCATE | TRUNCATEONLY}]]) 

DBCC shrinkfile ({fileid | 'filename'}, [compress_size 
                                     [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]]) 

DBCC sqlmgrstats 

DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} 
                                                  | {THREADS} | {LOGSPACE}) 

DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, 'THREADID'}] )] 

DBCC tab ( dbid, objid ) 

DBCC tape_control {'query' | 'release'}[,('\\.\tape')] 

DBCC tec [( uid[, spid[, ecid]] )] 

DBCC textall [({'database_name'|database_id}[, 'FULL' | FAST] )] 

DBCC textalloc ({'table_name'|table_id}[, 'FULL' | FAST]) 

DBCC thaw_io (db) 

DBCC traceoff [( tracenum [, tracenum ... ] )] 

DBCC traceon [( tracenum [, tracenum ... ] )] 

DBCC tracestatus (trace# [, ...trace#]) 

DBCC unpintable (dbid, table_id) 

DBCC updateusage ({'database_name'| 0} [, 'table_name' [, index_id]]) 
                                        [WITH [NO_INFOMSGS] [,] COUNT_ROWS] 

DBCC upgradedb (db) DBCC usagegovernor (command, value) 

DBCC useplan [(number_of_plan)] 

DBCC useroptions DBCC wakeup (spid) 

DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)

New Address

2009-02-05T11:54:00.001-03:00

Hi Fellows

I´m transfering my blog activities to the live space… it´s better to embed photos documents, etc… besides i got a plenty of space on SKy Drive, which is very cool. And i could stay in touch with my fellows around the globe. I hope you enjoy the new experience…

http://wandenkolk.spaces.live.com/blog/

Kind regards.

Wanden

it Worth a Click

2009-02-05T11:34:00.001-03:00

http://www.microsoft.com/azure/sql.mspx

Imagine do not have to have a database.. you only consume database services from a cloud of servers somewhere…

Obtaining stored execution plans from the Procedure Cache

2009-01-20T16:41:00.001-03:00

The question is, can we spy on procedure cache to see all the plans stored in there?

Yes we can! (by the way, congrats to the new president)

Select e.cacheobjtype, q.query_plan , DB_NAME(q.dbid) as database_name, e.refcounts, e.usecounts , (e.size_in_bytes / 1024 ) as size_in_kb from sys.dm_Exec_cached_plans as e Cross Apply sys.dm_exec_query_plan(e.plan_handle) as q

The query returns the following result:`(i know is a bit messy, but who cares?)

cacheobjtype                                       query_plan                                                                                                                                                                                                                                                       database_name                                                                                                                    refcounts   usecounts   size_in_kb
-------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -----------
Compiled Plan                                      <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[CleanExpiredJobs]
AS
SET NOCOUN ReportServer                                                                                                                     2           2           48
Compiled Plan                                      <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[CleanOrphanedSnapshots]
@Machine nvarcha ReportServer                                                                                                                     2           2           1096
Compiled Plan                                      <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[CleanExpiredCache]
AS
SET NOCOU ReportServer                                                                                                                     2           2           96
Compiled Plan                                      <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[DeleteExpiredPersistedStreams]
AS&# ReportServer                                                                                                                     2           2           56

sys.dm_os_wait_stats

2009-01-15T11:30:00.001-03:00

Regarding waiting for resources on sql server, one of the most valuable resources is the DMV above mentioned. But the point is, what on earth means the wait type. Follows an excerpt from SQL Server Books Online which explains the meaning of each one wait types..

Select * from sys.dm_os_wait_stats Order by Wait_time_ms DESC -- for example

wait_type                                                    waiting_tasks_count wait_time_ms         max_wait_time_ms     signal_wait_time_ms
------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------
LAZYWRITER_SLEEP                                             77093                77124252             1218                 7602
REQUEST_FOR_DEADLOCK_SEARCH                                  15425                77123140             5094                 77123140
...

The following table lists the wait types encountered by tasks.

Wait type
Description

ASYNC_DISKPOOL_LOCK

Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.

ASYNC_IO_COMPLETION

Occurs when a task is waiting for I/Os to finish.

ASYNC_NETWORK_IO

Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.

BACKUP

Occurs when a task is blocked as part of backup processing.

BACKUP_CLIENTLOCK

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

BACKUP_OPERATOR

Occurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type may indicate a hardware problem with the tape drive.

BACKUPBUFFER

Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.

BACKUPIO

Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.

BACKUPTHREAD

Occurs when a task is waiting for a backup task to finish. Wait times may be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type does not indicate a problem.

BAD_PAGE_PROCESS

Occurs when the background suspect page logger is trying to avoid running more than every five seconds. Excessive suspect pages cause the logger to run frequently.

BROKER_CONNECTION_RECEIVE_TASK

Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.

BROKER_ENDPOINT_STATE_MUTEX

Occurs when there is contention to access the state of a Service Broker connection endpoint. Access to the state for changes is serialized.

BROKER_EVENTHANDLER

Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.

BROKER_INIT

Occurs when initializing Service Broker in each active database. This should occur infrequently.

BROKER_MASTERSTART

Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.

BROKER_RECEIVE_WAITFOR

Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.

BROKER_REGISTERALLENDPOINTS

Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.

BROKER_SHUTDOWN

Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.

BROKER_TRANSMITTER

Occurs when the Service Broker transmitter is waiting for work.

BUILTIN_HASHKEY_MUTEX

May occur after startup of instance, while internal data structures are initializing. Will not recur once data structures have initialized.

CHECKPOINT_QUEUE

Occurs while the checkpoint task is waiting for the next checkpoint request.

CHKPT

Occurs at server startup to tell the checkpoint thread that it can start.

CLR_AUTO_EVENT

Occurs when a task is currently performing common language runtime (CLR) execution and is waiting for a particular autoevent to be initiated. Long waits are typical, and do not indicate a problem.

CLR_CRST

Occurs when a task is currently performing CLR execution and is waiting to enter a critical section of the task that is currently being used by another task.

CLR_JOIN

Occurs when a task is currently performing CLR execution and waiting for another task to end. This wait state occurs when there is a join between tasks.

CLR_MANUAL_EVENT

Occurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.

CLR_MONITOR

Occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.

CLR_RWLOCK_READER

Occurs when a task is currently performing CLR execution and is waiting for a reader lock.

CLR_RWLOCK_WRITER

Occurs when a task is currently performing CLR execution and is waiting for a writer lock.

CLR_SEMAPHORE

Occurs when a task is currently performing CLR execution and is waiting for a semaphore.

CLR_TASK_START

Occurs while waiting for a CLR task to complete startup.

CMEMTHREAD

Occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.

CURSOR

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

CURSOR_ASYNC

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

CXPACKET

Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

DBMIRROR_DBM_EVENT

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

DBMIRROR_DBM_MUTEX

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

DBMIRROR_EVENTS_QUEUE

Occurs when database mirroring waits for events to process.

DBMIRROR_SEND

Occurs when a task is waiting for a communications backlog at the network layer to clear to be able to send messages. Indicates that the communications layer is starting to become overloaded and affect the database mirroring data throughput.

DBMIRROR_WORKER_QUEUE

Indicates that the database mirroring worker task is waiting for more work.

DBMIRRORING_CMD

Occurs when a task is waiting for log records to be flushed to disk. This wait state is expected to be held for long periods of time.

DBTABLE

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

DEADLOCK_ENUM_MUTEX

Occurs when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that SQL Server is not running multiple deadlock searches at the same time.

DEADLOCK_TASK_SEARCH

Large waiting time on this resource indicates that the server is executing queries on top of sys.dm_os_waiting_tasks, and these queries are blocking deadlock monitor from running deadlock search. This wait type is used by deadlock monitor only. Queries on top of sys.dm_os_waiting_tasks use DEADLOCK_ENUM_MUTEX.

DEBUG

Occurs during Transact-SQL and CLR debugging for internal synchronization.

DISABLE_VERSIONING

Occurs when SQL Server polls the version transaction manager to see whether the timestamp of the earliest active transaction is later than the timestamp of when the state started changing. If this is this case, all the snapshot transactions that were started before the ALTER DATABASE statement was run have finished. This wait state is used when SQL Server disables versioning by using the ALTER DATABASE statement.

DISKIO_SUSPEND

Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish.

DLL_LOADING_MUTEX

Occurs once while waiting for the XML parser DLL to load.

DROPTEMP

Occurs between attempts to drop a temporary object if the previous attempt failed. The wait duration grows exponentially with each failed drop attempt.

DTC

Occurs when a task is waiting on an event that is used to manage state transition. This state controls when the recovery of Microsoft Distributed Transaction Coordinator (MS DTC) transactions occurs after SQL Server receives notification that the MS DTC service has become unavailable.

This state also describes a task that is waiting when a commit of a MS DTC transaction is initiated by SQL Server and SQL Server is waiting for the MS DTC commit to finish.

DTC_ABORT_REQUEST

Occurs in a MS DTC worker session when the session is waiting to take ownership of a MS DTC transaction. After MS DTC owns the transaction, the session can roll back the transaction. Generally, the session will wait for another session that is using the transaction.

DTC_RESOLVE

Occurs when a recovery task is waiting for the master database in a cross-database transaction so that the task can query the outcome of the transaction.

DTC_STATE

Occurs when a task is waiting on an event that protects changes to the internal MS DTC global state object. This state should be held for very short periods of time.

DTC_TMDOWN_REQUEST

Occurs in a MS DTC worker session when SQL Server receives notification that the MS DTC service is not available. First, the worker will wait for the MS DTC recovery process to start. Then, the worker waits to obtain the outcome of the distributed transaction that the worker is working on. This may continue until the connection with the MS DTC service has been reestablished.

DTC_WAITFOR_OUTCOME

Occurs when recovery tasks wait for MS DTC to become active to enable the resolution of prepared transactions.

DUMP_LOG_COORDINATOR

Occurs when a main task is waiting for a subtask to generate data. Ordinarily, this state does not occur. A long wait indicates an unexpected blockage. The subtask should be investigated.

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

EE_PMOLOCK

Occurs during synchronization of certain types of memory allocations during statement execution.

EE_SPECPROC_MAP_INIT

Occurs during synchronization of internal procedure hash table creation. This wait can only occur during the initial accessing of the hash table after the SQL Server instance starts.

ENABLE_VERSIONING

Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.

ERROR_REPORTING_MANAGER

Occurs during synchronization of multiple concurrent error log initializations.

EXCHANGE

Occurs during synchronization in the query processor exchange iterator during parallel queries.

EXECSYNC

Occurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such areas are bitmaps, large binary objects (LOBs), and the spool iterator. LOBs may frequently use this wait state.

FAILPOINT

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

FCB_REPLICA_READ

Occurs when the reads of a snapshot (or a temporary snapshot created by DBCC) sparse file are synchronized.

FCB_REPLICA_WRITE

Occurs when the pushing or pulling of a page to a snapshot (or a temporary snapshot created by DBCC) sparse file is synchronized.

FT_RESTART_CRAWL

Occurs when a full-text crawl needs to restart from a last known good point to recover from a transient failure. The wait lets the worker tasks currently working on that population to complete or exit the current step.

FT_RESUME_CRAWL

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

FULLTEXT GATHERER

Occurs during synchronization of full-text operations.

HTTP_ENDPOINT_COLLCREATE

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

HTTP_ENUMERATION

Occurs at startup to enumerate the HTTP endpoints to start HTTP.

HTTP_START

Occurs when a connection is waiting for HTTP to complete initialization.

IMP_IMPORT_MUTEX

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

IMPPROV_IOWAIT

Occurs when SQL Server waits for a bulkload I/O to finish.

INDEX_USAGE_STATS_MUTEX

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

IO_AUDIT_MUTEX

Occurs during synchronization of trace event buffers.

IO_COMPLETION

Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.

KSOURCE_WAKEUP

Used by the service control task while waiting for requests from the Service Control Manager. Long waits are expected and do not indicate a problem.

KTM_ENLISTMENT

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

KTM_RECOVERY_MANAGER

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

KTM_RECOVERY_RESOLUTION

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

LATCH_DT

Occurs when waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.

LATCH_EX

Occurs when waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.

LATCH_KP

Occurs when waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.

LATCH_NL

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

LATCH_SH

Occurs when waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.

LATCH_UP

Occurs when waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.

LAZYWRITER_SLEEP

Occurs when lazywriter tasks are suspended. This is a measure of the time spent by background tasks that are waiting. Do not consider this state when you are looking for user stalls.

LCK_M_BU

Occurs when a task is waiting to acquire a Bulk Update (BU) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_IS

Occurs when a task is waiting to acquire an Intent Shared (IS) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_IU

Occurs when a task is waiting to acquire an Intent Update (IU) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_IX

Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_RIn_NL

Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_RIn_S

Occurs when a task is waiting to acquire a shared lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_RIn_U

Task is waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_RIn_X

Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_RS_S

Occurs when a task is waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_RS_U

Occurs when a task is waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_RX_S

Occurs when a task is waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_RX_U

Occurs when a task is waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_RX_X

Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_S

Occurs when a task is waiting to acquire a Shared lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_SCH_M

Occurs when a task is waiting to acquire a Schema Modify lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_SCH_S

Occurs when a task is waiting to acquire a Schema Share lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_SIU

Occurs when a task is waiting to acquire a Shared With Intent Update lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_SIX

Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_U

Occurs when a task is waiting to acquire an Update lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_UIX

Occurs when a task is waiting to acquire an Update With Intent Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_X

Occurs when a task is waiting to acquire an Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LOGBUFFER

Occurs when a task is waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server.

LOGMGR

Occurs when a task is waiting for any outstanding log I/Os to finish before shutting down the log while closing the database.

LOGMGR_FLUSH

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

LOGMGR_QUEUE

Occurs while the log writer task waits for work requests.

LOGMGR_RESERVE_APPEND

Occurs when a task is waiting to see whether log truncation frees up log space to enable the task to write a new log record. Consider increasing the size of the log file(s) for the affected database to reduce this wait.

LOWFAIL_MEMMGR_QUEUE

Occurs while waiting for memory to be available for use.

MIRROR_SEND_MESSAGE

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

MISCELLANEOUS

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

MSQL_DQ

Occurs when a task is waiting for a distributed query operation to finish. This is used to detect potential Multiple Active Result Set (MARS) application deadlocks. The wait ends when the distributed query call finishes.

MSQL_SYNC_PIPE

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

MSQL_XACT_MGR_MUTEX

Occurs when a task is waiting to obtain ownership of the session transaction manager to perform a session level transaction operation.

MSQL_XACT_MUTEX

Occurs during synchronization of transaction usage. A request must acquire the mutex before it can use the transaction.

MSQL_XP

Occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends.

MSSEARCH

Occurs during Full-Text Search calls. This wait ends when the full-text operation completes. It does not indicate contention, but rather the duration of full-text operations.

NET_WAITFOR_PACKET

Occurs when a connection is waiting for a network packet during a network read.

OLEDB

Occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.

ONDEMAND_TASK_QUEUE

Occurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern.

PAGEIOLATCH_DT

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem.

PAGEIOLATCH_EX

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.

PAGEIOLATCH_KP

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem.

PAGEIOLATCH_NL

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

PAGEIOLATCH_SH

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.

PAGEIOLATCH_UP

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem.

PAGELATCH_DT

Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode.

PAGELATCH_EX

Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.

PAGELATCH_KP

Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode.

PAGELATCH_NL

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

PAGELATCH_SH

Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode.

PAGELATCH_UP

Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode.

PARALLEL_BACKUP_QUEUE

Occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.

PRINT_ROLLBACK_PROGRESS

Used to wait while user processes are ended in a database that has been transitioned by using the ALTER DATABASE termination clause. For more information, see ALTER DATABASE (Transact-SQL).

QNMANAGER_ACQUIRE

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

QPJOB_KILL

Indicates that an asynchronous automatic statistics update was canceled by a call to KILL as the update was starting to run. The terminating thread is suspended, waiting for it to start listening for KILL commands. A good value is less than one second.

QPJOB_WAITFOR_ABORT

Indicates that an asynchronous automatic statistics update was canceled by a call to KILL when it was running. The update has now completed but is suspended until the terminating thread message coordination is complete. This is an ordinary but rare state, and should be very short. A good value is less than one second.

QRY_MEM_GRANT_INFO_MUTEX

Occurs when Query Execution memory management tries to control access to static grant information list. This state lists information about the current granted and waiting memory requests. This state is a simple access control state. There should never be a long wait on this state. If this mutex is not released, all new memory-using queries will stop responding.

QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN

Occurs in certain cases when offline create index build is run in parallel, and the different worker threads that are sorting synchronize access to the sort files.

QUERY_NOTIFICATION_MGR_MUTEX

Occurs during synchronization of the garbage collection queue in the Query Notification Manager.

QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX

Occurs during state synchronization for transactions in Query Notifications.

QUERY_NOTIFICATION_TABLE_MGR_MUTEX

Occurs during internal synchronization within the Query Notification Manager.

QUERY_NOTIFICATION_UNITTEST_MUTEX

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

QUERY_OPTIMIZER_PRINT_MUTEX

Occurs during synchronization of query optimizer diagnostic output production. This wait type only occurs if diagnostic settings have been enabled under direction of Microsoft Product Support.

QUERY_TRACEOUT

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

RECOVER_CHANGEDB

Occurs during synchronization of database status in warm standby database.

RG_RECONFIG

Occurs when ALTER RESOURCE GOVERNOR RECONFIGURE or ALTER RESOURCE GOVERNOR DISABLE is called.

REPL_CACHE_ACCESS

Occurs during synchronization on a replication article cache. During these waits, the replication log reader stalls, and data definition language (DDL) statements on a published table are blocked.

REPL_SCHEMA_ACCESS

Occurs during synchronization of replication schema version information. This state exists when DDL statements are executed on the replicated object, and when the log reader builds or consumes versioned schema based on DDL occurrence.

REPLICA_WRITES

Occurs while a task waits for completion of page writes to database snapshots or DBCC replicas.

REQUEST_DISPENSER_PAUSE

Occurs when a task is waiting for all outstanding I/O to complete, so that I/O to a file can be frozen for snapshot backup.

REQUEST_FOR_DEADLOCK_SEARCH

Occurs while the deadlock monitor waits to start the next deadlock search. This wait is expected between deadlock detections, and lengthy total waiting time on this resource does not indicate a problem.

RESMGR_THROTTLED

Occurs when a new request comes in and is throttled based on the GROUP_MAX_REQUESTS setting.

RESOURCE_QUEUE

Occurs during synchronization of various internal resource queues.

RESOURCE_SEMAPHORE

Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.

RESOURCE_SEMAPHORE_MUTEX

Occurs while a query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests.

RESOURCE_SEMAPHORE_QUERY_COMPILE

Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.

RESOURCE_SEMAPHORE_SMALL_QUERY

Occurs when memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds, because the server transfers the request to the main query memory pool if it fails to grant the requested memory within a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries.

SEC_DROP_TEMP_KEY

Occurs after a failed attempt to drop a temporary security key before a retry attempt.

SERVER_IDLE_CHECK

Occurs during synchronization of SQL Server instance idle status when a resource monitor is attempting to declare a SQL Server instance as idle or trying to wake up.

SHUTDOWN

Occurs while a shutdown statement waits for active connections to exit.

SLEEP_BPOOL_FLUSH

Occurs when a checkpoint is throttling the issuance of new I/Os in order to avoid flooding the disk subsystem.

SLEEP_DBSTARTUP

Occurs during database startup while waiting for all databases to recover.

SLEEP_DCOMSTARTUP

Occurs once at most during SQL Server instance startup while waiting for DCOM initialization to complete.

SLEEP_MSDBSTARTUP

Occurs when SQL Trace waits for the msdb database to complete startup.

SLEEP_SYSTEMTASK

Occurs during the start of a background task while waiting for tempdb to complete startup.

SLEEP_TASK

Occurs when a task sleeps while waiting for a generic event to occur.

SLEEP_TEMPDBSTARTUP

Occurs while a task waits for tempdb to complete startup.

SNI_CRITICAL_SECTION

Occurs during internal synchronization within SQL Server networking components.

SNI_HTTP_ACCEPT

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

SNI_HTTP_WAITFOR_0_DISCON

Occurs during SQL Server shutdown, while waiting for outstanding HTTP connections to exit.

SOAP_READ

Occurs while waiting for an HTTP network read to complete.

SOAP_WRITE

Occurs while waiting for an HTTP network write to complete.

SOS_CALLBACK_REMOVAL

Occurs while performing synchronization on a callback list in order to remove a callback. It is not expected for this counter to change after server initialization is completed.

SOS_LOCALALLOCATORLIST

Occurs during internal synchronization in the SQL Server memory manager.

SOS_MEMORY_USAGE_ADJUSTMENT

Occurs when memory usage is being adjusted among pools.

SOS_OBJECT_STORE_DESTROY_MUTEX

Occurs during internal synchronization in memory pools when destroying objects from the pool.

SOS_PROCESS_AFFINITY_MUTEX

Occurs during synchronizing of access to process affinity settings.

SOS_RESERVEDMEMBLOCKLIST

Occurs during internal synchronization in the SQL Server memory manager.

SOS_SCHEDULER_YIELD

Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.

SOS_STACKSTORE_INIT_MUTEX

Occurs during synchronization of internal store initialization.

SOS_SYNC_TASK_ENQUEUE_EVENT

Occurs when a task is started in a synchronous manner. Most tasks in SQL Server are started in an asynchronous manner, in which control returns to the starter immediately after the task request has been placed on the work queue.

SOS_VIRTUALMEMORY_LOW

Occurs when a memory allocation waits for a resource manager to free up virtual memory.

SOSHOST_EVENT

Occurs when a hosted component, such as CLR, waits on a SQL Server event synchronization object.

SOSHOST_INTERNAL

Occurs during synchronization of memory manager callbacks used by hosted components, such as CLR.

SOSHOST_MUTEX

Occurs when a hosted component, such as CLR, waits on a SQL Server mutex synchronization object.

SOSHOST_RWLOCK

Occurs when a hosted component, such as CLR, waits on a SQL Server reader-writer synchronization object.

SOSHOST_SEMAPHORE

Occurs when a hosted component, such as CLR, waits on a SQL Server semaphore synchronization object.

SOSHOST_SLEEP

Occurs when a hosted task sleeps while waiting for a generic event to occur. Hosted tasks are used by hosted components such as CLR.

SOSHOST_TRACELOCK

Occurs during synchronization of access to trace streams.

SOSHOST_WAITFORDONE

Occurs when a hosted component, such as CLR, waits for a task to complete.

SQLCLR_APPDOMAIN

Occurs while CLR waits for an application domain to complete startup.

SQLCLR_ASSEMBLY

Occurs while waiting for access to the loaded assembly list in the appdomain.

SQLCLR_DEADLOCK_DETECTION

Occurs while CLR waits for deadlock detection to complete.

SQLCLR_QUANTUM_PUNISHMENT

Occurs when a CLR task is throttled because it has exceeded its execution quantum. This throttling is done in order to reduce the effect of this resource-intensive task on other tasks.

SQLSORT_NORMMUTEX

Occurs during internal synchronization, while initializing internal sorting structures.

SQLSORT_SORTMUTEX

Occurs during internal synchronization, while initializing internal sorting structures.

SQLTRACE_BUFFER_FLUSH

Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds.

SQLTRACE_LOCK

Occurs during synchronization on trace buffers during a file trace.

SQLTRACE_SHUTDOWN

Occurs while trace shutdown waits for outstanding trace events to complete.

SQLTRACE_WAIT_ENTRIES

Occurs while a SQL Trace event queue waits for packets to arrive on the queue.

SRVPROC_SHUTDOWN

Occurs while the shutdown process waits for internal resources to be released to shutdown cleanly.

TEMPOBJ

Occurs when temporary object drops are synchronized. This wait is rare, and only occurs if a task has requested exclusive access for temp table drops.

THREADPOOL

Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking unusually long, thus reducing the number of workers available to satisfy other batches.

TRACEWRITE

Occurs when the SQL Trace rowset trace provider waits for either a free buffer or a buffer with events to process.

TRAN_MARKLATCH_DT

Occurs when waiting for a destroy mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_EX

Occurs when waiting for an exclusive mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_KP

Occurs when waiting for a keep mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_NL

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

TRAN_MARKLATCH_SH

Occurs when waiting for a shared mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_UP

Occurs when waiting for an update mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRANSACTION_MUTEX

Occurs during synchronization of access to a transaction by multiple batches.

UTIL_PAGE_ALLOC

Occurs when transaction log scans wait for memory to be available during memory pressure.

VIEW_DEFINITION_MUTEX

Occurs during synchronization on access to cached view definitions.

WAIT_FOR_RESULTS

Occurs when waiting for a query notification to be triggered.

WAITFOR

Occurs as a result of a WAITFOR Transact-SQL statement. The duration of the wait is determined by the parameters to the statement. This is a user-initiated wait.

WAITSTAT_MUTEX

Occurs during synchronization of access to the collection of statistics used to populate sys.dm_os_wait_stats.

WORKTBL_DROP

Occurs while pausing before retrying, after a failed worktable drop.

WRITELOG

Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.

XACT_OWN_TRANSACTION

Occurs while waiting to acquire ownership of a transaction.

XACT_RECLAIM_SESSION

Occurs while waiting for the current owner of a session to release ownership of the session.

XACTLOCKINFO

Occurs during synchronization of access to the list of locks for a transaction. In addition to the transaction itself, the list of locks is accessed by operations such as deadlock detection and lock migration during page splits.

XACTWORKSPACE_MUTEX

Occurs during synchronization of defections from a transaction, as well as the number of database locks between enlist members of a transaction.

Delayed Write Message on SQL Server

2008-11-12T15:10:00.001-03:00

I was looking for some the messages mentioned by an incident and i found one that is quite common that SQL is taking more time to complete an io request.

So i found some good explanation on SQL Server Storage Engine blog.

Credits for them

-------------------------------------------------------------------------------

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000838. The offset of the latest long I/O is: 0x000000ebdc0000

You may hear this referred to as a stalled IO. I see questions on this warning message quite a bit, mostly about what’s wrong with SQL Server and why is SQL taking so long to read or write to the disk. Let me explain why this isn’t a SQL Server problem.

SQL Server does data file reads and writes almost exclusively as asynchronous IO, using the win32 APIs ReadFile, WriteFile, ReadFileScatter and WriteFileGather. Each of these APIs behave in a fairly similar manner – the caller sends in a handle to the file, some memory location to read or write, the size of the block and a structure that tells the kernel how to handle the IO. In SQL Server’s case the how to handle the IO is Asynchronously, please. The call returns immediately so that the thread issuing the IO can get out of the way and make life happy for some other user who’d also like their query to return.

The catch here is that ordinarily the time between the Asynchronous call to read or write and the completion of the read or write should be on the order of 10ms. Of course the longer it takes for an IO to return the more noticeable a performance impact there is to end users.

Prior to SQL Server 2000 SP4 the only way you would be able to tell if your IOs were taking longer than expected would be to use System Monitor and watch the PhysicalDisk\Avg Disk sec./read, write and transfer counter. This is a relatively acceptable method when the cause of your IO bottleneck is the latency of the physical disk, you might be surprised to find out that’s not the only thing that might slow down an IO!

Once a user mode application issues an IO request it’s the equivalent of putting a package in the mail, there’s nothing to do but wait. While you may not have anything to do once you’ve dropped the package the parcel carriers job has just begun, what with all the processing and labeling and transit – you get the idea. Likewise for the kernel. Once an IO transitions over to kernel mode it’s transformed into something called and IRP (interrupt request packet) and begins a trek down levels of filter drivers, virus scanners and device drivers before it finally makes its way home to a physical device.

Windows exposes methods for device and software manufactures to be notified of, and participate in, IOs. Filter drivers are one method of doing this, and it allows for great functionality of verifying you aren’t writing a file with a virus pattern, or backing up a file as it’s being written. The problem here is that the filter driver can hold up an IO for an extended period of time, and this time isn’t reflected in the Sysmon counter.

So what are you to do? You’ve put your specially crafted package in the mail, but the intended recipient still hasn’t got it.

The approach taken in SQL Server is to make note of the time the IO started, the offset of the IO within the file then check back a short time later to make sure all IOs have completed. If an IO is still outstanding and 15 seconds have elapsed then the warning is printed to the errorlog to alert the system administrator that something’s amiss.

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.

Table type parameters

2008-11-05T14:11:00.001-03:00

I was reading some artible and remembering...

One thing that my students always asked me, was "we can create a variable that is a table. How come can´t we assign a parameter to a function that is a table type?" Well, in SQL 2005 the problem is that is not possible to define inside the procedure one parameter that is a table.

Well on SQL 2008 now we can. There is an new element User-Defined Table Type. Is a data type that is actually a table definition. Look:

USE AdventureWorks2000
GO

-- Create the data type
CREATE TYPE dbo.SalesOrderItemType AS TABLE
(
    SalesOrderID    int,
    LineNumber    tinyint,
    ProductID    int,
    SpecialOfferID    int,
    CarrierTrackingNumber    nvarchar,
    OrderQty    smallint,
    UnitPrice    money,
    UnitPriceDiscount    float,
    ModifiedDate    datetime,
    rowguid    uniqueidentifier,
    LineTotal    float
)
GO

So you can create a procedure like this

CREATE PROCEDURE usp_insert_SalesOrderItem( @SalesItem dbo.SalesOrderItemType READONLY)
AS

... put some code here

In this way, you create a procedure that receives a table variagle as a parameter, the table variable passed as argument for that must comply with the same data type, to have the same columns.

Besides, the paramter received must have, in the stored procedure definition the readonly clause meaning that the table must have the ge only read.

HAve fun! Remember, try to get rid of cursors, use this kind of instrument to not use cursors on your procedures or other code.

Microsoft supports SQL Server in Virtualized Environment

2008-10-09T13:57:00.001-03:00

I was taking a look at the PSS Engineers blog, found out many interesting things there.

Specially that sql server is being supported on a virtualized environment, microsoft has changed the policy for that.

take a look at this article.

http://support.microsoft.com/kb/956893/en-us

enjoy

Microsoft SQL Server 2008 Cumulative Update

2008-10-07T16:12:00.001-03:00

http://support.microsoft.com/kb/956717/en-us

from pss engineers blog.

SQL Server Builds list

2008-03-31T10:36:00.001-03:00

This is quite insteresting. I'm posting an excerpt from Microsoft support about the Sql server builds released

for more detail take a look at http://support.microsoft.com/kb/937137/en-us

Build in progress

This cumulative update package is currently under development.
The parent build of this build will be determined as soon as this build is released.
For more information about this build, click the following article number to view the article in the Microsoft Knowledge Base:

949095 (http://support.microsoft.com/kb/949095/) Cumulative update package 7 for SQL Server 2005 Service Pack 2

Build 09.00.3228 (cumulative update)

This cumulative update package was released on February 18, 2008.
The parent build of this build is 09.00.3215.
For more information about the SQL Server 2005 post-Service Pack 2 hotfixes that are included in this build, click the following article number to view the article in the Microsoft Knowledge Base:

946608 (http://support.microsoft.com/kb/946608/) Cumulative update package 6 for SQL Server 2005 Service Pack 2

Build 09.00.3215 (cumulative update)

This cumulative update package was released on December 17, 2007.
The parent build of this build is 09.00.3200.
For more information about the SQL Server 2005 post-Service Pack 2 hotfixes that are included in this build, click the following article number to view the article in the Microsoft Knowledge Base:

943656 (http://support.microsoft.com/kb/943656/) Cumulative update package 5 for SQL Server 2005 Service Pack 2

Build 09.00.3209

This on-demand build was released on November 20, 2007.
The parent build of this build is 09.00.3208.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50002118
Contention occurs on a CMEMTHREAD wait type or on a spinlock.

Build 09.00.3208

This on-demand build was released on November 15, 2007.
The parent build of this build is 09.00.3206.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50002030
A CMEMTHREAD wait type causes high CPU utilization to occur under a load.

Build 09.00.3206

This on-demand build was released on November 8, 2007.
The parent build of this build is 09.00.3205.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001986
A feature has been added that logs silent conflicts that are caused by divergent metadata.

Build 09.00.3205

This on-demand build was released on November 7, 2007.
The parent build of this build is 09.00.3203.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001708
In SQL Server Management Studio, you cannot change the password for the db_securityadmin database role if the old password is not specified.

50001999
You cannot return to the original report in Windows Internet Explorer 7 after you open a report that accepts one or more DateTime parameters.

Build 09.00.3203

This on-demand build was released on September 30, 2007.
The parent build of this build is 09.00.3200.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001951
In SQL Server Management Studio, you cannot change the password for the db_securityadmin database role if the old password is not specified.

50001993
You cannot return to the original report in Internet Explorer 7 after you open a report that accepts one or more DateTime parameters.

50001997
The new batching model that was introduced in SQL Server 2005 SP2 causes an out-of-memory exception when you run the Rebuild Index Task.

50001998
Event ID 17055 messages that an instance of SQL Server 2000 forwards to an instance of SQL Server 2005 are not received by the instance of SQL Server 2005.

50002000 (This bug is a variation of bug number 50000314.)
The Save dialog box in Report Builder stops responding in SQL Server 2005 SP2. This problem occurs if you start Report Builder when a report name is specified in the URL in Report Builder.

Build 09.00.3200 (cumulative update)

This cumulative update package was released on October 15, 2007.
The parent build of this build is 09.00.3195.
For more information about the SQL Server 2005 post-Service Pack 2 hotfixes that are included in this build, click the following article number to view the article in the Microsoft Knowledge Base:

941450 (http://support.microsoft.com/kb/941450/) Cumulative update package 4 for SQL Server 2005 Service Pack 2

Build 09.00.3195

This on-demand build was released on September 19, 2007.
The parent build of this build is 09.00.3194.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001812
A null reference exception occurs when you set parameters on a Web part that is linked to a document library part.

Build 09.00.3194

This on-demand build was released on September 11, 2007.
The parent build of this build is 09.00.3186.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001766
942087 (http://support.microsoft.com/kb/942087/)
A C# application that was built by using SQL Server 2005 Integration Services build 9.00.3042 crashes, and an access violation occurs inDtspipeline.dll.

50001619
An internal exception occurs when when you process indexes.

50001625
In SQL Server 2005 Analysis Services Service Pack 2 (SP2), you experience frequent access violations in the PFCompressIterator::DecompressArray function under stress conditions.

50001713
Report Manager drill-down (+/-) does not work consistently with Snapshots.

50001550
Merge non-convergence occurs between the Republisher and the Publisher.

Build 09.00.3186 (cumulative update)

This cumulative update package was released on August 20, 2007.
The parent build of this build is 09.00.3182.
For more information about the SQL Server 2005 post-Service Pack 2 hotfixes that are included in this build, click the following article number to view the article in the Microsoft Knowledge Base:

939537 (http://support.microsoft.com/kb/939537/) Cumulative update package 3 for SQL Server 2005 Service Pack 2

Build 09.00.3182

This build is no longer available. Please use build 3186 or higher. This on-demand build was released on July 17, 2007.
The parent build of this build is 09.00.3180.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001298
938712 (http://support.microsoft.com/kb/938712/)
In Analysis Services 2005, a full process of a database may not process all the records for some dimensions

50001324
940129 (http://support.microsoft.com/kb/940129/)
An MDX query may not return results unless "non empty" is specified with the dimension that has unnatural hierarchy

50001440
940128 (http://support.microsoft.com/kb/940128/)
SQL Server may fail to generate a plan for a complex query that involves cursors and error 8623 occurs

Build 09.00.3180

This build is no longer available. Please use build 3186 or higher. This on-demand build was released on July 11, 2007.
The parent build of this build is 09.00.3179.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001392
939942 (http://support.microsoft.com/kb/939942/)
FIX: You receive an error message when you try to access a report after you configure SQL Server 2005 Reporting Services to run under the SharePoint integrated mode

Build 09.00.3179

This build is no longer available. Please use build 3186 or higher. This on-demand build was released on July 11, 2007.
The parent build of this build is 09.00.3178.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001482
The TokenAndPermUserStore cache store may continue to grow steadily and decrease performance

50001194
938243 (http://support.microsoft.com/kb/938243/)
Executing a full-text query against a catalog that is being rebuilt may fail with index corruption errors

Build 09.00.3178

This build is no longer available. Please use build 3186 or higher. This on-demand build was released on July 5, 2007.
The parent build of this build is 09.00.3177.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001193
938086 (http://support.microsoft.com/kb/938086/)
Running concurrent SQL Server Agent jobs in the context of proxy account may fail with errors

50001352
938086 (http://support.microsoft.com/kb/938086/)
Running concurrent SQL Server Agent jobs in the context of proxy account may fail with errors

Build 09.00.3177

This build is no longer available. Please use build 3186 or higher. This on-demand build was released on June 29, 2007.
The parent build of this build is 09.00.3175.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001391
939562 (http://support.microsoft.com/kb/939562/)
A query that contains an INSTEAD OF trigger may fail with error 8624 in SQL Server 2005 SP2

50001367
938363 (http://support.microsoft.com/kb/938363/)
Non-convergence may occur in merge replication when there are join filters for partition groups and the articles belong to multiple publications

50001379
939285 (http://support.microsoft.com/kb/939285/)
An incorrect error message may be returned by the SQL Native Client provider when in sync mode and there is a query time-out

50001408
939564 (http://support.microsoft.com/kb/939564/)
Querying a text column that contains more than 1,024 characters may return incorrect results when you use the SQL Native Client provider

50001397
939563 (http://support.microsoft.com/kb/939563/)
An UPDATE query or a DELETE query in a Merge Replication topology may fail with error 8152

Build 09.00.3175 (cumulative update)

This cumulative update package was released on June 18, 2007.
The parent build of this build is 09.00.3166. For more information about the SQL Server 2005 post-Service Pack 2 hotfixes that are included in this build, click the following article number to view the article in the Microsoft Knowledge Base:

936305 (http://support.microsoft.com/kb/936305/) Cumulative update package 2 for SQL Server 2005 Service Pack 2 is available

Build 09.00.3169

This on-demand build was released on May 18, 2007.
The parent build of this build is 09.00.3166.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001251
937041 (http://support.microsoft.com/kb/937041/)
FIX: Changes in the publisher database are not replicated to the subscribers in a transactional replication if the publisher database runs exposed in a database mirroring session in SQL Server 2005

50001248
937033 (http://support.microsoft.com/kb/937033/)
FIX: Error message when you run a linked server query in SQL Server 2005: "The oledbprovider unisys.dmsII.1 for linkserver '<ServerName>' reported an error the provider ran out of memory"

Build 09.00.3166

This on-demand build was released on May 7, 2007.
The parent build of this build is 09.00.3162.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001020
936185 (http://support.microsoft.com/kb/936185/)
FIX: Blocking and performance problems may occur when you enable trace flag 1118 in SQL Server 2005 if the temporary table creation workload is high

50000726
932106 (http://support.microsoft.com/kb/932106/)
FIX: Error message when you run a DMX query that uses a linked server to query data from an instance of Analysis Services in SQL Server 2005: "The following system error occurred: Logon failure: unknown user name or bad password"

50001024
934734 (http://support.microsoft.com/kb/934734/)
FIX: A database is marked as suspect when you update a table that contains a nonclustered index in SQL Server 2005

50000924
936863 (http://support.microsoft.com/kb/936863/)
FIX: Some MDX queries may run slower and consume more memory after you install SQL Server 2005 Analysis Services Service Pack 2

50000925
936262 (http://support.microsoft.com/kb/936262/)
In SQL Server 2005 Analysis Services, the cache is cleaned even though the memory usage is less than the LowMemoryLimit property that is configured.

50000898
936261 (http://support.microsoft.com/kb/936261/)
FIX: The process stops responding and the CPU utilization reaches 100 percent when you process a partition in SQL Server 2005 Analysis Services

50000802
932180 (http://support.microsoft.com/kb/932180/)
FIX: The Cluster Discrimination tab is empty when you browse a clustering model of SQL Server 2005 Analysis Services in SQL Server BIDS

50000920
935341 (http://support.microsoft.com/kb/935341/)
FIX: Error message when you run an MDX query against a measure in SQL Server 2005 Analysis Services: "An internal error has occurred"

50000732
932331 (http://support.microsoft.com/kb/932331/)
You receive an error message when you try to build a mining model that contains lots of data on a 32-bit server that is running SQL Server 2005 Analysis Services.

50000890
936254 (http://support.microsoft.com/kb/936254/)
FIX: An MDX query may be slow if it reads from an aggregation that has less than 4,096 records in SQL Server 2005 Analysis Services

Build 09.00.3162

This on-demand build was released on April 23, 2007.
The parent build of this build is 09.00.3156.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50001001
932610 (http://support.microsoft.com/kb/932610/)
FIX: Error message when you run an MDX query in SQL Server 2005 Analysis Services: "An unexpected error occurred (file 'pffilestore.cpp', line 3267, function 'PFFileStore::HandleDataPageFault')"

50001056
935922 (http://support.microsoft.com/kb/935922/)
FIX: Error message when you install Microsoft Dynamics CRM 3.0: "Setup failed to validate specified Reporting Services Report Server"

50000926
935360 (http://support.microsoft.com/kb/935360/)
FIX: Error message when you run an MDX query that retrieves data from an Analysis Services database: "An error occurred while the dimension, with the ID of '<DatabaseName>', Name of '<DimensionName>' was being processed"

50000923
935829 (http://support.microsoft.com/kb/935829/)
FIX: You receive incorrect results when you run an MDX query against a dimension that contains a parent-child hierarchy in SQL Server 2005 Analysis Services

50000871
935830 (http://support.microsoft.com/kb/935830/)
FIX: A server may start slowly if you have SQL Server 2005 Analysis Services installed and if many objects are stored on the server

50000927
A value from the SQL Server 2005 Analysis Services database may be larger than the value from the source database.

50000899
935831 (http://support.microsoft.com/kb/935831/)
When you configure proactive caching for a partition in SQL Server 2005 Analysis Services, the underlying partition may be processed two times during a proactive caching update.

50000885
935832 (http://support.microsoft.com/kb/935832/)
FIX: You cannot cancel an MDX query that runs for a long time in SQL Server 2005 Analysis Services

Build 09.00.3161 (cumulative update)

This cumulative update package was released on April 16, 2007.
The parent build of this build is 09.00.3156.
For more information about the SQL Server 2005 post-Service Pack 2 hotfixes that are included in this build, click the following article number to view the article in the Microsoft Knowledge Base:

935356 (http://support.microsoft.com/kb/935356/) Cumulative update package (build 3161) for SQL Server 2005 Service Pack 2 is available

Build 09.00.3159

This on-demand build was released on April 4, 2007.
The parent build of this build is 09.00.3156.
This build contains the following SQL Server 2005 post-SP2 hotfix.

SQL Hotfix bug number
Knowledge Base article number
Description

50001012
934459 (http://support.microsoft.com/kb/934459/)
FIX: The Check Database Integrity task and the Execute T-SQL Statement task in a maintenance plan may lose database context in certain circumstances in SQL Server 2005 builds 3150 through 3158

Build 09.00.3156

This cumulative update package was released on March 23, 2007.
The parent build of this build is 09.00.3155.
This build contains the following SQL Server 2005 post-SP2 hotfix.

SQL Hotfix bug number
Knowledge Base article number
Description

50000958
934226 (http://support.microsoft.com/kb/934226/)
FIX: Error message when you try to use Database Mail to send an e-mail message in SQL Server 2005: "profile name is not valid (Microsoft SQL Server, Error 14607)"

Build 09.00.3155

This on-demand build was released on March 21, 2007.
The parent build of this build is 09.00.3154.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50000900
933549 (http://support.microsoft.com/kb/933549/)
FIX: You may receive an access violation when you perform a bulk copy operation in SQL Server 2005

50000964
933724 (http://support.microsoft.com/kb/933724/)
FIX: The query performance is slow when you run a query that uses a user-defined scalar function against an instance of SQL Server 2005

50000875
932115 (http://support.microsoft.com/kb/932115/)
FIX: The ghost row clean-up thread does not remove ghost rows on some data files of a database in SQL Server 2005

50000969
933766 (http://support.microsoft.com/kb/933766/)
FIX: Failed assertion message in the Errorlog file when you perform various operations in SQL Server 2005: "Failed Assertion = 'fFalse' Attempt to access expired blob handle (3)"

50000919
When you run an encrypted stored procedure, error 565 may occur. The error message for error 565 is as follows: "A stack overflow occurred in the server while compiling the query. Please simplify the query."

50000918
933808 (http://support.microsoft.com/kb/933808/)
FIX: Error message when you run a query that contains nested FOR XML clauses in SQL Server 2005: "The XML data type is damaged"

50000921
933499 (http://support.microsoft.com/kb/933499/)
FIX: Error message when you use transactional replication to replicate the execution of stored procedures to subscribers in SQL Server 2005: "Insufficient memory to run query"

Build 09.00.3154

This on-demand build was released on March 21, 2007.
The parent build of this build is 09.00.3153.
This build contains the following SQL Server 2005 post-SP2 hotfixes.

SQL Hotfix bug number
Knowledge Base article number
Description

50000948
934106 (http://support.microsoft.com/kb/934106/)
FIX: SQL Server 2005 database engine generates failed assertion errors when you use the Replication Monitor to monitor the distribution database

50000952
934188 (http://support.microsoft.com/kb/934188/)
FIX: The Distribution Agent does not deliver commands to the Subscriber even if the Distribution Agent is running in SQL Server 2005

50000949
934109 (http://support.microsoft.com/kb/934109/)
FIX: The Distribution Agent generates an access violation when you configure a transactional replication publication to run an additional script after the snapshot is applied at the subscriber in SQL Server 2005

50000960
A content query from the Data Mining Client for Excel add-in may take a long time to display the result set. This problem occurs especially when the result set is large. Microsoft Office Excel may appear to stop responding (hang) when the query is running.

Build 09.00.3153

This on-demand build was released on March 15, 2007.
The parent build of this build is 09.00.3152.
This build contains the following SQL Server 2005 post-SP2 hotfix.

SQL Hotfix bug number
Knowledge Base article number
Description

50000945
933564 (http://support.microsoft.com/kb/933564/)
FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005

Build 09.00.3152

This cumulative hotfix package was released on March 15, 2007.
The parent build of this build is SQL Server 2005 SP2 (09.00.3042).
For more information about the SQL Server 2005 post-Service Pack 2 hotfixes that are included in this build, click the following article number to view the article in the Microsoft Knowledge Base:

933097 (http://support.microsoft.com/kb/933097/) Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2 is available

Montoring sql server agent jobs execution

2008-02-13T10:34:00.001-03:00

To monitor sql server agent job execution not using the gui, you can do the following

SELECT program_name, login_time, host_name
FROM sys.dm_exec_sessions
WHERE status = 'running'

or use the following sp

EXEC msdb.[dbo].[sp_help_job]
@execution_status = 1

Jobs

2008-02-13T10:32:00.001-03:00

i am back on teaching classes business... that is a good thing.

one and most iimportant is that i got the obligation to study all the time to keep my classes ready to be taught and my mind always working.

There is a plenty of microsoft official courses of sql server, many of them are short trainings, but very interesting ones.

Actually, i guess is an interesting strategy, because dividing the courses can make a more deep training about a subject.

@@servername and SERVERPROPERTY('servername')

2008-01-29T09:59:00.001-03:00

This two sql structures are suposed to return the same information, but in some cases they don´t.

There is something about where @@servername ans serverproperty(‘servername’) picks up the information. @@servername picks from sysservers, and (I don´t know why) this can be different from serverproperty, that returns the machinename plus the instance name.

SERVERPROPERTY(‘servername’) reports changes of network machine name, while @@servername don’t´, you shoul change it manually using sp_dropserver and sp_addserver.

There is some information on books online about that.

Cool SQL Locking detection queries

2008-01-09T14:04:00.001-03:00

-- Detaction Queries Locking

-- this one uses the dm_tran_locks, just converts some information of that

SELECT
   L.resource_type,
   DB_NAME(L.resource_database_id) as DatabaseName,
   L.resource_associated_entity_id,
   L.request_session_id,
   L.request_mode,
   L.request_status
FROM sys.dm_tran_locks AS L
WHERE L.request_status = 'WAIT'
ORDER BY DatabaseName, L.request_session_id;

-- more descriptive

SELECT
   w.session_id AS waiting_session_id,
   DB_NAME(w.resource_database_id) AS DatabaseName,
   w.wait_duration_ms,
   w.waiting_task_address,
   t.request_mode,
   t.resource_type,
   t.resource_associated_entity_id,
w.resource_description AS lock_resource_description,
   w.wait_type,
   w.blocking_session_id,
   w.resource_description AS blocking_resource_description
FROM sys.dm_os_waiting_tasks AS w
   JOIN sys.dm_tran_locks AS t
   ON w.resource_address = t.lock_owner_address
WHERE w.wait_duration_ms > 5000
   AND w.session_id > 50;

-- full descriptive with the locked query

SELECT
   W.session_id AS waiting_session_id,
   DB_NAME(T.resource_database_id) AS DatabaseName,
   W.wait_duration_ms,
   W.waiting_task_address,
   t.request_mode,
   (SELECT SUBSTRING(ST.text, (req.statement_start_offset/2) + 1,
      ((CASE req.statement_end_offset
         WHEN -1 THEN DATALENGTH(ST.text)
         ELSE req.statement_end_offset
        END - req.statement_start_offset)/2) + 1)
   FROM sys.dm_exec_requests AS req
      CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS st
   WHERE req.session_id = T.request_session_id)
      AS waiting_query_text,
   t.resource_type,
   t.resource_associated_entity_id,
   w.wait_type,
   w.blocking_session_id,
   w.resource_description AS blocking_resource_description,
   CASE WHEN W.blocking_session_id > 0 THEN
      (SELECT ST2.text FROM sys.sysprocesses AS Pr
             CROSS APPLY sys.dm_exec_sql_text(pr.sql_handle) AS ST2
      WHERE pr.spid = w.blocking_session_id)
      ELSE NULL
   END AS blocking_query_text
FROM sys.dm_os_waiting_tasks AS w
   JOIN sys.dm_tran_locks AS t
   ON w.resource_address = t.lock_owner_address
WHERE w.wait_duration_ms > 5000
   AND w.session_id > 50;

Remember that only works on sql server 2005 or above.

lock escalation problems

2008-01-09T10:15:00.001-03:00

I was reading something about lock escalation on kb. And one thing that called my attention is a simple way to break a large and complex query that deletes hundreds of records in a much smarter one... look at this

from http://support.microsoft.com/kb/323630/en-us

Break up large batch operations into several smaller operations. For example, suppose you ran the following query to remove several hundred thousand old records from an audit table, and then you found that it caused a lock escalation that blocked other users:

DELETE FROM LogMessages WHERE LogDate < '2/1/2002'

By removing these records a few hundred at a time, you can dramatically reduce the number of locks that accumulate per transaction and prevent lock escalation. For example:

SET ROWCOUNT 500
delete_more:
     DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

end of microsoft kb
ps. That's the shit!