Wandenkolk & SQL Server

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!

SQL Server is SLOW

2008-01-07T09:22:00.001-03:00

Holy...

When you got an incident like that there is a problem, the sql server do not respond, the client is in a hurry.

Unfortunaltly SQL Server 2000 is a black box for many events that could make a server to respond slowly due to wait events.

The situation classical for wait events is when server is slow, or not responding as desired, and you got free cpu, io, network bandwidth....

i found this article regarding this issues, may be useful sometime for someone, including myself :)

http://www.simple-talk.com/sql/performance/sql-server-wait-events-taking-the-guesswork-out-of-performance-profiling/

Thanks to Mario Broodbakker

ps. SQL Server 2005 has DMVs which helps a LOT, on doing performance troubleshooting. And SQL Server 2008 will (at least it should) be even better.

Enjoy!

Autoparameterization

2008-01-04T10:03:00.001-03:00

This one is interesting on SQL Server, it allows a given execution plan to be reused among many similar ad-hoc queries. but is very important to sql server to watch if the parameter may have some influence on the number of rows. The index selection is the same for all the parametrized queries, so if one query returns one row and another execution of the same query returns many rows it can be a problem. In this cases the parameterization is considered unsafe.

Here are some examples of unsafe (for parameterization) structures in SQL Server:

JOIN
BULK INSERT
IN lists
UNION
INTO
FOR BROWSE
OPTION <query hints>
DISTINCT
TOP
WAITFOR statements
GROUP BY, HAVING, COMPUTE
Full Text operators (Contains / Freetext)
Subqueries
FROM clause of a SELECT statement has table valued return or full-text table or OPENROWSET or OPENXML or OPENQUERY or OPENDATASOURCE --> Derived queries
Comparison of the form EXPR <> a non-null constant

You can take a look of the parameterization operations in sql server by running this query

-- Excerpt from book Inside SQL Server: Query Tuning and Optimization

USE Northwind2
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 6;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 2;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO

The dm_exec_cached_plans can show some information about the stored plans and the form and parametrization information about them.

Still talking about safe and unsafe parameterization operations, there is a property on databases called Parameterization. It can be set simple or forced. Forced means that sql server will take as parameters all the constants inside your query with few exceptions. the benefit is you compile less queries... so you save cpu time for other operations.

I may take for example a financial system that i was doing a tuning for sql server, in one hour the system sends to sql server thousands of small queries, setting parameterization forced save a lot of cpu time and memory.

The dark side of the force is that when you use parametrized plans, for queries that maybe have different cardinalities the plans might not be optimal, in such way that your perofrmance is affected.

Then? what is the right path?

Young padawan, the right way is always test, test with simple, and monitor with profiler, and check the plans... test with forced see the cpu use, and check the plans... always testing and choosing based on testing experience. It depends on a lot of factors, the server, workload, application architecture... and so on.

More Hints to Take a look in Detail!

2008-01-03T14:31:00.001-03:00

Group Hints - like HASH GROUP, make some influence in group by operations (dahhh)

Union -- look for MERGE UNION

MAXDOP - nothing to do about dopping (ha ha ha), but have influence on paralelism for queries. IS VERY useful sometimes.. i remember when i was a consultant, when we got a problem that for no reason the thing just don't work, always the PSS from microsoft say to put MAXDOP(0) in order to disable the paralelism. It was a long time ago in a Galaxy far far away...

EXPAND VIEWS: is useful when you are running queries in conjunction with indexed views (resource only available on enterprise edition)

INDEX - You can force indexes. but remember, if you force a plan an forget it on a application that plan might be good on a given situation but databases are dynamic, so your plan might be bad in another situation of your db, so be careful. in fact, the problem of forcning is inherent to any optimizer hint...

Force Order Hint

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

This one is cool.

SELECT O.[OrderId], O.[CustomerId] FROM [Customers] C JOIN [Orders] O JOIN [Employees] E ON O.[EmployeeId] = E.[EmployeeId] ON C.[CustomerId] = O.[CustomerId] WHERE C.[City] = N'London' AND E.[City] = N'London' OPTION (FORCE ORDER, HASH JOIN)

__________________________________________________

The force order hint, instructs sql server the order that the join should be resolved. On join resolution process, sql server analyzes the number of rows on each table and decides which one will be the inner table, and the outer table. With force order sql server will use the order in the query. It could be good in some cases, when the optimizer cannot detect the best plan, but could be bad, because you can mislead the optimizer best choice. Besides the HASH JOIN hint forces the use o a given method, you can use LOOP, HASH or MERGE.

Again, the choice for a method to solve the join depends on cardinality factors that sql server analyzes using the statistics and available indexes.

Have Fun!

ps. If you want more details about that, read Inside SQL Server 2005: Query Tuning and Optimization by Kalen Delaney

Fast optimizer hint clause

2008-01-03T13:22:00.001-03:00

Look at this

SELECT [OrderId], [CustomerId], [OrderDate]
FROM [Orders]
ORDER BY [OrderDate]

-- Table 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT [OrderId], [CustomerId], [OrderDate]
FROM [Orders]
ORDER BY [OrderDate]
OPTION (FAST 1)

--Table 'Orders'. Scan count 1, logical reads 1724, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

__________________________________________________

The fast option says to the optimizer how many records the query should return. So, in this case, the plan is less efficient because the query returns all the rows on the table, and the plan is instructed to 'think' that it will return only one row. the fast hint, is interesting when we want to predict to the optimizer how many rows the query might return on its execution.

Another hint may be used to instruct the optimizer about the value of a parameter. Is the Optimized FOR hint. Look at the example:

DECLARE @ShipCode nvarchar(20)
SET @ShipCode = N'05022'
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = @ShipCode

-- Table 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

DECLARE @ShipCode nvarchar(20)
SET @ShipCode = N'05022'
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = @ShipCode
OPTION (OPTIMIZE FOR (@ShipCode = N'05022'))

--Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The optimize for option means that the execution plan should be optimized fro the @shipcode variable having the value '05022'. And results in a more efficient execution plan.

Improving Performance on SQL Server

2008-01-03T10:45:00.001-03:00

When you will be trying to improve performance in queries remember this:

1. Rewrite your query... think about using functions on columns can eliminate the use of indexes... subqueries, correlated and nocorrelated can limit the choices for the query optimizer, instead of it you may choose to put the results of subqueries on independent queries and variables... and use these variables later.

Besides, avoid cursors... sql server is good on dealing with groups of records... cursor is a mechanism to use on row at the time. no good.

2. schema improvements. think about normalize and denormalize accordingly with the use of your application (OLAP or OLTP). Indexes, statistics, composite indexes, covering indexes .. bla bla bla..

all of this you may know.. but you don't know how many time i saw these obvious mistakes happens, so it may be important to remember.

3. Statistics. The path to the optimizer to choose the right index is statistics... so choose the way you update if you do so automatically or manually, and the sample rate ... like full scan, is useful sometimes.. but keep in mind the overhead to do so.

4. Indexes. Create indexes in low density columns. Keep in mind that sql server will not use your index if no query uses a selective predicate and sometimes it depends on the density the column, please... i've seen indexes created on a column Gender... gender is Male or Female... sql server will not use this index (in a 50% / 50% aprox. relation)

Fowarding Pointer or Forwarding Record

2008-01-03T10:34:00.001-03:00

Forwarding record is a way the sql server does to not have to move the record for another location when updates occurs and it reorganizes the leaf level of a physical structure. It's like the signs say that you moved to another address...

But there is a pay for that... every time the scan is being made, the sql server, when it trips on a forwarding pointer it jumps to the real address and get back to continue the scan... so... it makes a simple scan much more complicated.

here is a query to help you to check if you have a lot of these forwarding pointers

SELECT forwarded_record_count
FROM sys.dm_db_index_physical_stats
   (db_id('Northwind2'), object_id('neworders'), null, null, 'detailed')

DBCC SHOWSTATISTICS

2008-01-02T15:44:00.001-03:00

Holy!! i almost forgot this one.

let me explain.. i was reading th Inside SQL Server 2005: Query Tuning and Optimizing. and at certain point i tripped with DBCC SHOWSTATISTICS

try this

DBCC SHOW_STATISTICS('Orders', 'cust_date_indx')

Very Nice!!! the statistics is the catch to sql server do the right choice for indexes, after the index selection the statustics can help to discover if the index is suitable for a query.

do some search job in the google and read a book... the statiscs is a must on sql server!

Regards!

Change Tracking on SQL 2008

2008-01-01T14:34:00.001-03:00

One feature that will be on SQL Server's next release is database change tracking.

it allows to create applications that track changes in databases without using mechanisms like replication or triggers and so on.

How it works?

After a DML operation is made on database the following actions happen:

Performs the data insert, update, or delete operation.

Adds a row to an internal change tracking table. There is one internal table for each user table that uses change tracking.

so the change tracking is based on logging information in data tables to capture what is happening.

There is a bunch of functions to check the data that is been tracked *:

CHANGETABLE (CHANGES)

Returns tracking information for all changes to a table that have occurred since a specified version.

CHANGETABLE (VERSION)

Returns the latest change tracking information for a specified row.

CHANGE_TRACKING_MIN_VALID_VERSION()

Returns the minimum version that is valid for use in obtaining change tracking information from the specified table when you are using the CHANGETABLE function.

CHANGE_TRACKING_CURRENT_VERSION

Obtains a version that is associated with the last committed transaction. You can use this version the next time you enumerate changes by using CHANGETABLE.

CHANGE_TRACKING_IS_COLUMN_IN_MASK

Interprets the SYS_CHANGE_COLUMNS value that is returned by the CHANGETABLE(CHANGES …) function.

WITH CHANGE_TRACKING_CONTEXT

Enables the specification of a change context, such as an originator ID, when an application changes data.

* Excerpt from books online, pre-release material.

there is new database options for the DBA control the change tracking feature (enabled or not) and the period of retention for the information about the changes.

(check on database properties, change tracking tab)

Happy New Year!

2008-01-01T13:28:00.001-03:00

Hello Everyone... last month i have to apologize. The blog was as little bit without updates... the holidays and all the related stuff.. but this year we got a lot of stuff to have fun.

The sql server 2008 will be lauched soon and we have a lot of things to check out.

to start i sugest you take a look at pauls randall's blog (http://www.sqlskills.com/blogs/paul/2007/12/31/InsideTheStorageEngineWhenDoVersioningTagsGetAdded.aspx) this article in the hyperlink discusses the myth about and overhead on using snapshot isolation level. When you are in a snapshot transaction and you got multiple versions of rows some of these versions are marked as ghost records, and you can identify that via dbcc page.

Enjoy! and Happy 2008 for everyone!

Finnally i'm settle down!

2007-12-12T10:57:00.001-03:00

Finnally i'm settle down with my new routine on the new job. I got a minute or two to write on the blog.

SQL Server 2008 is very close to be released. But do not expect major changes on this release. I've installed the november ctp and one of the thing that brought to my attention was the new installer, fully intergrtated with all the tools included.

in the past two weeks i was striving to configure a cluster with Virtual Server. I got a lot work trying to accomplish that... and the problems were minor ones.. like windows update.. dns ... domain.. and stuff like that.

be smart with sql server 2008 release.. one of the most interesting features is the Transparent data encryption. It allows you to create a database that only can be attached on a server that has a proper encryption key. This way, if by accident, the data is stolen somehow, you can asure that this information is useless because is fully encrypted, and without a proper key, cannot be used.

There is an interesting article on database journal about that improvements on sql server 2008. Unfornatly there is a little or no techincal details about how to do it. I did some tests with data encryption, i will put it here later... just let me take som time to find it on my computer...

That's all folks