Booking an Appointment in NIMS, Hyderabad.
For all those who would like to book an appointment at NIMS for consultancy with Department Heads, they can call +91 40 23396552 and +91 40 23489244. The consultation fee is 300/- and starts at 16:30hrs till 19hrs.
SQL Server Management Studio Tips
Starting SSMS is easy..just Run ‘ssms’. This client tool for SQL Server helps managing the SQL Server a comfortable task. One can align the tabs in a manner that suits their kind of work.
Tip1 : Integrate BOL as a tab in the SSMS : This can be done using Tools -> Options -> Help . In the Show Help using field select ” Integrated help viewer”. So when ever you press F1, the SSMS wouldnt start a new window but opens the BOL as a tab. Search also opens as a tab as you can see in the SSMS image above.
Tip2 : Most of the times I would run sp_who2 to know the current connections on the server. Keyboard shortcuts can be configured for stored procedures through the Options->keyboard window.
Tip 3: The Template Explorer [ To your right in the image], is a feature which helps in standardizing your queries. Ctrl+Alt+T shall open the Template Explorer. You can drag and drop a template into your Query tab. Then press Ctrl+Shift+M to fill the Template parameters. For creating your own templates, the following link shall help.
Tip 4: In SSMS 2008, the ‘go’ statement takes a int parameter. This is a SSMS and SQLCMD feature and may not be a change in the actual Query Engine. The parameter is like a simple loop so the statement can execute those many times.
Tip 5: For all of us who use shortcuts, a good feature is to have the shortcuts displayed beside the tasks/tooltips themselves to be aware the next time. To configure rightclick on Toolbar+ customize. Check the “Show Screentips on toolbars” and “Show shortcut keys in screentips”.
Tip 6: Most people know the common Ctrl+Tab to move between query tabs. Also Alt+F6 can be used to move within Tab groups. Ctrl+R to hide/unhide Result window. Also F6 can be used to move between Frames.
Tip 7: To automatically register local servers in Registered Servers tab, rightclick on Localservers Group ->Tasks->Register Local servers. Also we can export the registered servers so they can be imported to other servers’ management studio. This would be of great help in passing the registered servers on to other machines.
Tip 8: At times we need to save the Gridresultset into a CSV file. By default the column header isnt copied .To have the Column header copied as well, go to Tools-> Options>Query Results>SQL Server> check the “Include Column headers when copying or saving the results.” One more import screen in the Tools->Options is the Text Editor->Transact SQL> General. This form contains settings for Word wrap, line numbers. The Intellisense form can be used to disable Intellisense just incase you are scripting directly on a Prod Server.
Tip 9: Intellisense has been a visible addendum to SSMS 2008. Sometimes we would want to disable for a session and this can be done by Rightclick on Query window and click Intellisense Enabled.
Keep in mind that all the above tips work in SSMS 2008 but some wont on SSMS 2005.
SQL Server Standard Edition vs Enterprise Edition
Many a times a Solution Architect would fall in a dilemma whether to go with SQL Server 2005 Standard Edition or Enterprise Edition especially when the price difference is so high.
The best place to head to before looking into the Budget is to head to BOL – check – ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm This link would list the features present and not but is a lengthy one.
As a summary, if we have to work on huge DBs with loads of Binary/ Large Objects then EE could be preferred because of advanced Indexing and pre-caching. SE is limited in terms of Cluster nodes[<2] and instances[<16] but these are rarely a limitation. if the environment is 32 bit and large memory support is implemented , EE is preferred as it Dynamically manages AWE. Remember this can be possible only on Win 2003 onwards.
Other possible maintenance tasks where EE is preferred would be Online Indexing and Parallel Indexing and DBCC . These features are useful in mission critical and round-the-clock used servers. Also the Online Page and secondary File restore would help in quick recovery.
Other important performance boosters in EE are Table and Indexed Partitioning and Enhanced Read-ahead and Scan.These are particularly useful when the Tables hold large number of records. Still, Query tuning cant be surrogated by upgrading to EE. The Query Plan needs to be well tuned prior to going for EE and make use of the Index Partitioning and parallel operations. It has be kept in mind, that when there are lots of concurrent users making heavy operations it would be sometimes better to have Parallellism disabled [Degree of Parallelism to 1].
Incase of BI and Analysis services, EE is most preferable for many of the purposes. The above listed BOL page and http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx would provide tabular list of features.
Renaming a Database? Its easy…
Yes..Its easy in SQL Server to rename a database. Just search in the Books Online for “Rename Database” and you get the procedures.
Here are some precautions after renaming a Database :
1) Check for any Maintenance Plans or Jobs using/optimizing this Database. Those jobs need to be corrected.
2) If there are any Logins on the Database with default DB as the old DB that needs to be corrected as well. This problem arises because master..syslogins stores the DBName as the default DB rather than the dbid. any particular reason why?? Please let me know incase…
SQL Server services
Depending on the components that you decide to install, SQL Server Setup installs the following services:
- SQL Server Database Services – The service for the SQL Server relational Database Engine.
- SQL Server Agent – Executes jobs, monitors SQL Server, fires alerts, and enables automation of some administrative tasks.
- Analysis Services – Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.
- Reporting Services – Manages, executes, creates, schedules, and delivers reports.
- Integration Services – Provides management support for Integration Services package storage and execution.
- SQL Server Browser – The name resolution service that provides SQL Server connection information for client computers.
- Full-text search – Quickly creates full-text indexes on content and properties of structured and semistructured data to provide document filtering and word-breaking for SQL Server.
- SQL Server Active Directory Helper – Publishes and manages SQL Server services in Active Directory.
- SQL Writer – Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.
Increasing Max worker threads – A negative impact
There are many instances when Administrators come to think that increasing Max Worker Threads shall increase performance on a server with thousands of users. This might not be always and preferable not to touch this configuration setting unless we are backed by a lot of data, research and testing.
Increasing the Max Worker threads shall directly influence the MTL sizing as the MTL is computed by the 256[default]+0.5*[Max Worker Threads] MB. Incase the MWT is increased the actual memory that might be needed for the Buffer Pool might be lying unused in the MTL.
Also, increasing the MWT shall increase the Thread contention at the UMS schedulers and this inturn shall result in a negative impact on the performance.
For a better understanding on the UMS architecture and the influence of MWT on it, check out here and also the Books Online.
Query Timeouts – A client side feature only.
Yes thats true.. The feature of a Query timeout is a Client side feature and the Database engine doesnt have any involvement in it except accepting a “Attention event” from the Client.
Once the SQL Server database engine receives a Attention Event, it tries to stop serving to the session at the earliest but not immediately. This would take care of any locks that need to be freed before aborting the query execution.
For a better understanding of this Event and Query Timeouts check out Ken Hendersen’s blog
Concurrency enhancements for the tempdb database
Here is a great link that describes Concurrency enhancements for the tempdb database. This also helps understand the way an object gets a page in the memory.
“During object creation, two (2) pages must be allocated from a mixed extent and assigned to the new object. One page is for the Index Allocation Map (IAM), and the second is for the first page for the object. SQL Server tracks mixed extents by using the Shared Global Allocation Map (SGAM) page. Each SGAM page tracks about 4 gigabytes of data.
As part of allocating a page from the mixed extent, SQL Server must scan the Page Free Space (PFS) page to find out which mixed page is free to be allocated. The PFS page keeps track of free space available on every page, and each PFS page tracks about 8000 pages. Appropriate synchronization is maintained to make changes to the PFS and SGAM pages; and that can stall other modifiers for short periods.
When SQL Server searches for a mixed page to allocate, it always starts the scan on the same file and SGAM page. This results in intense contention on the SGAM page when several mixed page allocations are underway, which can cause the problems documented in the “Symptoms” section of this article. ”
A single GAM page for 4GB of Tempdb file means there is heavy contention for that page. This is the same with the PFS as well where the contention is doubled with allocation and deallocation of pages. This is main reason to have multiple Tempdb files and with the same size. IF any single Tempdb file is bigger again it can be frequently contended for during the round-robin algorithm for allocating a page.
Also, “The auto-grow of tempdb data files can also interfere with the proportional fill algorithm. Therefore, it may be a good idea to turn off the auto-grow feature for the tempdb data files. If the auto-grow option is turned off, you must make sure to create the data files so that they are large enough to prevent the server from experiencing a lack of disk space with tempdb.”
Trace Flags in SQL Server 2008.
A brief description of Traceflags in SQL 2008.
260 – Prints versioning for DLLs for XPs.
1204 – Resources involved in Deadlocks and also Victim details.
1211 – No lock escalation at all.
1222 – Resources involved in Deadlock in XML format and also victims details.
1224 – No lock escalation unless severe memory pressure.
2528 – No parallellization of DBCC Commands.
3205 – No compressed dumps to tapes though file compression is enabled on Tapes.
3226 – No messages on Backups in the Errorlog.
3608 – Doesnt start any DB than Master on Startup.
3625 – Limits error message
4616 – Server level metadata visible to App roles.
6527 – No memory dumps in CLR integration on first error occurance.
7806 – Enable DAC on SQL Express.
Enable/Disable traceflag using DBCC Traceon and DBCC Traceoff. While Server startup use the -T startup option to SQLSrvr.exe. Use DBCC Tracestatus to find the traces enabled on the server.
Dont confuse the functions involving Profiler Traces with those of these Traceflags. The ones that involve Traceflags are DBCC commands.
Problems with Mixed Extents.
SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage
SQL Server 2008 optimized mixed extent allocation behavior reducing the need for trace flag 1118 and the contention on SGAM(s). The logic was also added to SQL Server 2005 in a CU release, KB article 936185.
The MORE INFORMATION section of the article states: “The hotfix that this article describes affects only the tempdb database. Additionally, you do not have to have trace flag 1118 after you install this hotfix. However, you can still use trace flag 1118.”
Some have interpreted this to mean the 1118 trace flag has been deprecated. This is NOT the case and I have asked for the text to be updated. Trace flag 1118 may still be needed along with the other suggestions in section “Troubleshooting contention caused by to DML operations” ofhttp://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx.
The blog that discusses this is here.
Visitors to this site.
Duty Free!! 