Wednesday, March 31, 2010

INDEX USAGE ANALYSIS REPORT

How to generate report for Index Physical statistics

Here is an interesting information about how to generate report for Index Usage

Check the below link and enjoy the usage of indexes and recommendations using one click report!!


http://blog.sqlauthority.com/2010/03/26/sql-server-generate-report-for-index-physical-statistics-ssms/






SQL Server 2008 - New Features

New Features in MS SQL Server 2008 - Database Administrators

The below link provides us an insight about the new features in MS SQL Server 2008 for Database Administrators.

Have a look at this link and enjoy the new features!

http://sqlcat.com/top10lists/archive/2009/01/30/top-10-sql-server-2008-features-for-the-database-administrator-dba.aspx

DBCC Commands

How to Check the List of DBCC Commands using SQL Server

Get a list of all the DBCC commands both documented and undocumented.


Solution: Turn on trace 2520 and use DBCC HELP ('?') to get a list of all the commands.

The script:

dbcc traceon(2520)
dbcc help ('?')
GO

Shows the list of DBCC commands. then

DBCC HELP()

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)

DMV List

Here We Go.......................

The Complete List of DMV/DMF present in SQL Server 2005 and 2008.




DMV/DMF Name20052008
dm_audit_actions
X
dm_audit_class_type_map
X
dm_broker_activated_tasksXX
dm_broker_connectionsXX
dm_broker_forwarded_messagesXX
dm_broker_queue_monitorsXX
dm_cdc_errors
X
dm_cdc_log_scan_sessions
X
dm_clr_appdomainsXX
dm_clr_loaded_assembliesXX
dm_clr_propertiesXX
dm_clr_tasksXX
dm_cryptographic_provider_properties
X
dm_database_encryption_keys
X
dm_db_file_space_usageXX
dm_db_index_usage_statsXX
dm_db_mirroring_auto_page_repair
X
dm_db_mirroring_connectionsXX
dm_db_mirroring_past_actions
X
dm_db_missing_index_detailsXX
dm_db_missing_index_group_statsXX
dm_db_missing_index_groupsXX
dm_db_partition_statsXX
dm_db_persisted_sku_features
X
dm_db_script_level
X
dm_db_session_space_usageXX
dm_db_task_space_usageXX
dm_exec_background_job_queueXX
dm_exec_background_job_queue_statsXX
dm_exec_cached_plansXX
dm_exec_connectionsXX
dm_exec_procedure_stats
X
dm_exec_query_memory_grantsXX
dm_exec_query_optimizer_infoXX
dm_exec_query_resource_semaphoresXX
dm_exec_query_statsXX
dm_exec_query_transformation_statsXX
dm_exec_requestsXX
dm_exec_sessionsXX
dm_exec_trigger_stats
X
dm_filestream_file_io_handles
X
dm_filestream_file_io_requests
X
dm_fts_active_catalogsXX
dm_fts_fdhosts
X
dm_fts_index_populationXX
dm_fts_memory_buffersXX
dm_fts_memory_poolsXX
dm_fts_outstanding_batches
X
dm_fts_population_rangesXX
dm_io_backup_tapesXX
dm_io_cluster_shared_drivesXX
dm_io_pending_io_requestsXX
dm_os_buffer_descriptorsXX
dm_os_child_instancesXX
dm_os_cluster_nodesXX
dm_os_dispatcher_pools
X
dm_os_dispatchers
X
dm_os_hostsXX
dm_os_latch_statsXX
dm_os_loaded_modulesXX
dm_os_memory_allocationsXX
dm_os_memory_brokers
X
dm_os_memory_cache_clock_handsXX
dm_os_memory_cache_countersXX
dm_os_memory_cache_entriesXX
dm_os_memory_cache_hash_tablesXX
dm_os_memory_clerksXX
dm_os_memory_node_access_stats
X
dm_os_memory_nodes
X
dm_os_memory_objectsXX
dm_os_memory_poolsXX
dm_os_nodes
X
dm_os_performance_countersXX
dm_os_process_memory
X
dm_os_ring_buffersXX
dm_os_schedulersXX
dm_os_spinlock_stats
X
dm_os_stacksXX
dm_os_sublatchesXX
dm_os_sys_infoXX
dm_os_sys_memory
X
dm_os_tasksXX
dm_os_threadsXX
dm_os_virtual_address_dumpXX
dm_os_wait_statsXX
dm_os_waiting_tasksXX
dm_os_worker_local_storageXX
dm_os_workersXX
dm_qn_subscriptionsXX
dm_repl_articlesXX
dm_repl_schemasXX
dm_repl_tranhashXX
dm_repl_traninfoXX
dm_resource_governor_configuration
X
dm_resource_governor_resource_pools
X
dm_resource_governor_workload_groups
X
dm_server_audit_status
X
dm_tran_active_snapshot_database_transactionsXX
dm_tran_active_transactionsXX
dm_tran_commit_table
X
dm_tran_current_snapshotXX
dm_tran_current_transactionXX
dm_tran_database_transactionsXX
dm_tran_locksXX
dm_tran_session_transactionsXX
dm_tran_top_version_generatorsXX
dm_tran_transactions_snapshotXX
dm_tran_version_storeXX
dm_xe_map_values
X
dm_xe_object_columns
X
dm_xe_objects
X
dm_xe_packages
X
dm_xe_session_event_actions
X
dm_xe_session_events
X
dm_xe_session_object_columns
X
dm_xe_session_targets
X
dm_xe_sessions
X