SQL Server Troubleshooting Tips and Tricks
From SQLServerPedia:http://sqlserverpedia.com/wiki/SQL_Server_Troubleshooting_Tips_and_Tricks
SQL Server Tweaks and Tools That Make a DBA’s Life Easier This article shares some of the tweaks and tools that can make the job of a SQL Server database administrator (DBA) easier. You’ll find tips for all of the following:
IndexesIf you use “included” columns, you know the frustration associated with figuring out which columns are included. The following stored procedures can help:
Take a look at all of these and use the one best meets your needs. Keyboard ShortcutsTo choose a keyboard scheme, in SQL Server Management Studio (SSMS), select Tools | Options… | Environment | Keyboard. Keyboard Shortcuts
Alt+F1 sp_help Ctrl+1 sp_who Ctrl+1 sp_lock Here are some suggestions for additional shortcuts: Ctrl+3 Select Top 100 * From Ctrl+4 sp_tables @table_owner = ‘dbo’ Ctrl+5 sp_columns Ctrl+6 sp_stored_procedures @sp_owner = ‘dbo’ Ctrl+7 sp_spaceused Ctrl+8 sp_helptext Ctrl+9 dba_indexLookup_sp or sp_helpindex2 Please note that any changes you make to these settings will not take effect until you open a new query window. Here’s an example of how you could use these shortcuts:
You may want to remove or change the schema filters if you use schemas other than dbo. Query Execution SettingsSMSS offers advanced settings to help prevent unintentional issues in production environments, such as a query that causes locking or blocking. To access these options in SSMS, choose Tools | Options… | Query Execution | SQL Server | Advanced. Query Execution Settings
You can also make these same setting changes in Visual Studio. Copy BehaviorThis tip is not specific to SQL Server; it’s useful for any Microsoft product. Holding down “Alt” while you drag your mouse will change your selection behavior to block selection. Block Selection
Object Detail ExplorerOne of the great updates available in SQL Server 2008 is the Object Detail Explorer. For example, you can quickly find the table size and row counts of all the tables in a particular database. The Object Detail Explorer requires SQL 2008 Management Studio, but you can connect SQL 2008 SSMS to a 2005 instance. Note: If these options are not visible, right-click the column headers and add them to the display. Object Detail Explorer
Missing IndexesIf you use SSMS 2008 to execute Display Estimated Query Plan (Ctrl+L), it will show whether you’re missing any indexes. This will even work if you connect SSMS 2008 to SQL 2005. Missing Index Author CreditsThis wiki article was adapted from a blog post by Michelle Ufford. Michelle is a SQL Developer DBA for GoDaddy.com, where she works with high-volume, mission-critical databases. She has over a decade of experience in a variety of technical roles and has worked with SQL Server for the last 5 years. She enjoys performance tuning and maintains an active SQL Server blog. Her online presences include:
|