Bru Medishetty's SeekWell Blogs
Posts tagged Performance Tuning
My First Article in MSSQLTips
Feb 11th
Today is a special day for me. An article that I submitted is published in MSSQLTips. The article looks into the Index fill factor settings and what is a good value for a given scenario.
Continue to read the article Understanding SQL Server Index Fill Factor Setting.
– Bru Medishetty
Performance Tuning in SQL Server
Jan 5th
One of the frequently asked questions in forums, interviews and during general discussions, is performance tuning. In fact, a friend of mine, (who knows a little bit of SQL Server) when speaking about my blogs, asked me why I had not written any blog on performance tuning. I gave a pause and said, do you know that heavy usage of cursors result in poor performance, Database partitioning results in slightly better performance and Database Snapshots cause a performance overhead? Then I continued, though you see I had a blog on those topics, it is not noticed that they are related to performance of the SQL Server.
The point is, there are many actions or the usage of certain features which directly or indirectly affect the performance of a SQL Server. I would like to post blogs on performance tuning SQL Server, it may be Query Tuning such as rewriting the query to perform better or looking at Indexes and ensure that there are proper Indexes that help the query. It can be related to locking / blocking happening on the database or improper settings of the database options.
There are certain other things to look at when looking to tune a SQL Server apart from those mentioned above. An improper placement of the database files can lead a performance degradation or excessive usage of cursors. There can be system bottlenecks such as Memory, Processor, Disk or Network that is the prime cause for a system slowness.
Many of these shall be addressed individually in the forth-coming blogs.
– Bru Medishetty
Enable AWE Option in SQL Server
Nov 16th
In this article we will look at the steps to enable AWE Option in SQL Server. When enabled, the Advanced Windowing Extensions gives the ability to SQL Server to utilize Memory more than 4 GB. AWE enabling for SQL Server is valid in 32 Bit Systems only, on 64 bit systems AWE is not required to be enabled.
Steps to enable AWE
In the SSMS, right click on the SQL Instance in Object Explorer, and select properties. the following Properties Dialog Box is displayed.
In the left Pane of the Box, Select the Memory Page and Under Server Memory options check the box as shown in the picture below to use AWE to allocate memory. You can also specify a Minimum and Maximum Memory SQL Server can utilize on the machine. So that incase of Memory available to SQL Server it does not complete utilize and resulting in no or less memory to the Operating System and other application running on the Server.
Select OK in the Properties page. The change in the setting will not be effective upon restarting SQL Server.
Alternately, you can also enable using sp_configure. The following scripts provide the alternate steps to
perform to enable AWE option.
As always, I suggest referring other technical information before enabling AWE and other system settings that need to be enabled on the Server for this to be successful, such as enabling /3GB switch or /pae switch in Boot.ini etc.
–Bru Medishetty
Recent Comments