Most Wanted

SQL SERVER

Below I have listed some frequently used objects. I will keep updating this list. You can also add new objects as comments for this page. I will then update the page.

Keep the code (Blits code to know your new Environment) handy. It will help when you are employed into a new SQL Server environment.

To keep SQL up to date with latest service packs, refer http://sqlserverbuilds.blogspot.in/

USEFUL TABLES AND VIEWS

1. master..sysperfinfo
2. <db>..sysindexes
3. master..sysprocesses
4. sys.databases
5. syslogins
6. sys.messages
7. sys.dm_exec_requests
8. sys.dm_exec_sql_text
9. sys.dm_exec_connections
10. sys.dm_db_index_physical_stats
11. sys.dm_db_missing_index_groups
12. sys.dm_db_missing_index_details
13. sys.dm_db_missing_index_group_stats
14. sys.dm_exec_query_resource_semaphore
15. sys.dm_exec_query_memory_grants
16. sysindexes – Using this you can count the rows in a table faster. make use of below query.
    

     select rows from sysindexes
     where id= OBJECT_ID('tablename') and indid < 2

USEFUL STOREDPROCS

1. sp_spaceused
2. sp_helpdb
3. sp_helpfile
4. sp_change_users_login
5. sp_changedbowner
6. sp_readerrorlog

USEFUL FUNCTIONS

1. fn_virtualfilestats()

USEFUL LINKS

1. Port numbers a DBA should know.
2. SQL Server DBA Interview Questions.
3. Audit default trace and catch culprits

LOG FILE IO BOTTLE NECK

1. DBCC OPENTRAN
2. DBCC SQLPERF(LOGSPACE)
3. fn_virtualfilestats
4. sys.dm_os_wait_stats
5. sys.dm_io_virtual_file_stats
6. sys.dm_io_pending_io_requests
7. Performance Counters:
   1. SQL Server:Databases (Log Bytes Flushed/sec, Log Flushes/sec, Log Flush Wait Time)
   2. Average Disk sec/Read (The average time, in milliseconds, of a read of data from disk, read latency) should be less than 20 ms.
   3. Average Disk sec/Write (The average time, in milliseconds, of a write of data from disk read latency) should be less than 20 ms.
8. Find the number of VLFs from “DBCC LOGINFO (database_name)”. Log performance begin to degrade when number of VLFs goes beyond 100 or 200. Shrink the file and then manually grow the file to optimum size in phases to minimize the number of VLFs. VLF count increases when the autogrow happens on a tlog file. File should be expanded to required size thereby reducing the number of autogrows to prevent this from happening. If the log file is large, it is recommended to grow the file by 2GB/4GB at a time to prevent large VLFs. Large VLFs will take long time to be cleared and so the VLFs should not be more than 500MB.
   • Number of VLFs created are based on below size scale
     • Log file growth of less than 64 MB results in 4 new VLFs.
     • A growth between 64 MB and 1 GB results in 8 VLFs.
     • More than 1 GB growths creates 16 VLFs for each growth.

Ref: http://www.bradmcgehee.com/wp-content/uploads/presentations/St{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}20Louis_Inside{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}20the{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}20SQL{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}20Server{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}20Transaction{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}20Log.pdf