Databases Reference
In-Depth Information
you are facing, you may ask some other seemingly obvious questions just to confirm or rule out
various considerations:
1.
Is SQL Server the only application running on the server box? Running SQL Server is
a memory- and CPU-intensive process. If you share it with a domain controller, Exchange,
HR, or some other enterprise applications, it probably cannot perform optimally.
2.
Have you analyzed some CPU, Memory, and I/O counters? If you are running a mission-
critical, resource-intensive database on an underpowered server, no amount of server
tweaking and tuning can fix the problem.
3.
Related to the question above, do you have a good physical design of the data? Is data placed
appropriately on different spindles and I/O paths so you can maximize simultaneous I/O?
Is your application designed appropriately (having short transactions, appropriate
isolation levels, set operations as opposed to processing one row at a time, useful indexes,
and so on)?
4.
Is there anti-virus software on the server? Normally, database boxes are behind the firewall
and properly patched. Performance-wise, it is not always a good idea to have virus-scanning
software on the database box. Sometimes there is no other way around it. If that is the case,
is the scanning scheduled to occur during less busy times? Have the database files, the ones
having
.mdf
,
.ndf
,and
.ldf
extensions, been excluded from the scanning process? Another
way to achieve that is to exclude the database folder from the scanning list.
5.
Is the firmware on your hardware up-to-date? This includes the BIOS, disk controller
firmware, fire-channel drivers, and other storage devices and their respective drivers. Most,
if not all, of the hardware requires the latest version of firmware or drivers to function
properly and at the optimal level. So it is very important to keep them up-to-date.
Now that the disclaimer is out of the way, it is time to get started with server-level configuration settings.
Inspecting Current SQL Server Settings
For all things SQL Server, there are generally two ways to do things: via the graphical user interface
(GUI) (whether it is SQL Server Management Studio, Configuration Manager, or others) or via scripts.
You will take a look at inspecting server settings via the GUI interface first.
InspectingServer SettingswithSQL Server
Management Studio
For most server parameters, you can see and change their values using SQL Server Management Studio.
Here are the steps to do it:
1.
Connect to the database server in question with Management Studio.
2.
Within Object Explorer, right-click the server itself, and select Properties from the
context menu.
3.
You will see the Server Property window (shown in Figure 7-1). From here, you can
inspect your server parameter settings. Click different sections in the Select a Page Frame,
and you will see settings pertaining to the property you select.
Search WWH ::
Custom Search