SQL Server Management Studio小技巧
来源:互联网 发布:天龙八部多开器虚拟mac 编辑:程序博客网 时间:2024/05/02 20:50
SQL Server Troubleshooting Tips and Tricks
- Page
- Discussion
- View source
- History
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:
Contents
- 1 Indexes
- 2 Keyboard Shortcuts
- 3 Query Execution Settings
- 4 Copy Behavior
- 5 Object Detail Explorer
- 6 Missing Indexes
- 7 Author Credits
- 8 Related Reading
Indexes
If you use “included” columns, you know the frustration associated with figuring out which columns are included. The following stored procedures can help:
- sp_helpindex – A system stored procedure that reports information about the indexes on a table or view
- sp_helpindex2 – A rewrite of the sp_helpindex stored procedure, written by Kimberly Tripp
- dba_indexLookup_sp – A custom, non-system stored procedure, written by Michelle Ufford
Take a look at all of these and use the one best meets your needs.
Keyboard Shortcuts
To choose a keyboard scheme, in SQL Server Management Studio (SSMS), select Tools | Options… | Environment | Keyboard.
Keyboard Shortcuts
The “Standard” keyboard scheme has the following shortcuts by default:
Alt+F1 sp_helpCtrl+1 sp_whoCtrl+1 sp_lock
Here are some suggestions for additional shortcuts:
Ctrl+3 Select Top 100 * FromCtrl+4 sp_tables @table_owner = ‘dbo’Ctrl+5 sp_columnsCtrl+6 sp_stored_procedures @sp_owner = ‘dbo’Ctrl+7 sp_spaceusedCtrl+8 sp_helptextCtrl+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:
- Use Ctrl+4 to find a list of tables.
- Copy one into your query window.
- Highlight the table name (I usually double-click on it) and press Ctrl+3 to view a sample of that table’s data.
You may want to remove or change the schema filters if you use schemas other than dbo.
Query Execution Settings
SMSS 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
Some suggestions include:
- Change “SET TRANSACTION ISOLATION LEVEL” to “READ UNCOMMITTED.” This will minimize the impact of your ad-hoc queries by allowing dirty reads. While this can be beneficial for many production environments, make sure to understand the implications of this setting before implementing.
- Change “SET DEADLOCK_PRIORITY” to “Low.” This will tell SQL Server to select your session as the victim in the event of a deadlock.
- Change “SET LOCK TIMEOUT” to a smaller, defined value, such as 30000 milliseconds (30 seconds). By default, SQL Server will wait forever for a lock to be released. By specifying a value, SQL Server will abort after the specified timeout period when a lock is encountered.
You can also make these same setting changes in Visual Studio.
Copy Behavior
This 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 Explorer
One 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 Indexes
If 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 Credits
This 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:
- SQLServerPedia Profile: Sqlfool
- Blog: http://sqlfool.com/
- Twitter: http://twitter.com/sqlfool/
- SQL Server Management Studio小技巧
- SQL Server Management Studio
- SQL Server Management Studio
- SQL Server Management Studio 安装
- 没有SQL Server Management Studio
- sql server management studio快捷键
- 教程:SQL Server Management Studio
- 找不到sql server management studio的解决方案
- 改变 SQL Server Management Studio的模板
- SQL Server Management Studio 键盘快捷键
- SQL Server 2005 Management Studio安装失败
- 编写SQL Server Management Studio插件
- 编写SQL Server Management Studio插件2
- Microsoft SQL Server Management Studio Express
- SQL Server Management Studio 的一个bug
- microsoft sql server management studio (express)
- SQL Server Management Studio 键盘快捷键
- Microsoft SQL Server Management Studio Express
- jQuery+Ajax+Struts2.0
- 多线程
- SEO必杀技:如何准确预测热门关键词
- :(:)
- ASP.net 验证码(C#)
- SQL Server Management Studio小技巧
- 原创与拷贝
- 分享C#高端视频教程WPF讲座——Application类的使用
- .net 中常用到的一些正则表达式。
- 分享C#高端视频教程WPF讲座——创建第一个WPF实例
- sql server2005 的windows验证模式
- W3WP.EXE的解决方法
- IE和Firefox的键盘事件
- struts启动流程,ajax的缓存问题