SQL Q&ADaylight Saving T ime, Server Memory, and More
Edited by Nancy Michell
Daylight Saving Time
Q Given the upcoming changes to Daylight Saving Time (DST) in the U.S. to comply with the Energy Policy Act of 2005, should I update SQL Server™?
A No. At this time there are no SQL Server-specific updates required to support the changes to DST. SQL Server relies on the underlying operating system for time-related data, which means if the OS is reporting the correct dates and times, SQL Server will report and use the same values. To comply with the upcoming changes to DST, you will be required to update your copies of Windows® as outlined at
. This will be required on all Windows operating systems prior to Windows Vista™ (which already includes the changes) to comply with the DST changes, including those running SQL Server. (Australia is also experiencing some changes. See
Connecting with Windows Vista
Q I've installed Windows Vista and now I can't connect to SQL Server 2005 on my system. I'm a local administrator, and I used to be able to make this connection without any problem. What has happened?
A This is expected behavior in Windows Vista and SQL Server 2005 without Service Pack 2 (SP2). Windows Vista has a new security model (User Account Control) that in effect traps your membership in the local administrator's group and asks you to verify operations requiring administrative privileges. If you click the Allow button, your credentials, which include your administrator token, are sent along to the application. In the case of the SQL Server Management Studio (SSMS), you're not shown a dialog because just running the tool doesn't require administrative permissions. The problem is that by default the SQL Server 2005 system administrator role includes the local administrators group from the operating system, and that is what your account used in the past to access SQL Server. Since Windows Vista isn't sending that permission along, you don't gain access.
It should be noted that running SQL Server 2005 without SP2 isn't supported on Windows Vista, and that SP2 has a tool that will add your account for you. If you're still waiting for SP2, the fix is pretty simple. Your individual Windows user account must be added to SQL Server, in your case to the system administrator's role. To do this, just right-click the SQL Server Management Studio and select Run as Administrator. Connect to your SQL Server and add your Windows account to the system administrator's role. See
SQL Server Books Online
for more information on how that works.
Transactional Replication with Views
Q If I have a published view and I update it, I know that transaction will be replicated. If I update the base table of this view, however, will that transaction be replicated? Also, if I have a published table and create a view of it, and I update the view instead of the base table, will that transaction replicate?
A Assuming the base table is configured as an article in a publication (that is, you configured the base table for replication as well), then any update to the base table would be replicated.
When you replicate a view, by default all that is replicated as part of a view is the schema portion of the view, or the code behind the view, not the data that underlies it (unless you have an indexed view). Then, even without replication, any time you update a view-in this case that means running a Data Manipulation Language (DML) statement with the view as the target-you are really just updating data in the underlying table, not the view. A view is just a logical storage of a query statement with no physical storage attached to it (again, unless you are using indexed views).
Max Server Memory
Q I have a box running Windows Server® 2003 and SQL Server 2000, with 5GB of RAM. Let's say I use the /3GB switch to increase my user mode virtual address space, the /PAE switch to load the Physical Address Extension (PAE) version of the Windows kernel, and set Address Windowing Extensions (AWE) Enabled to 1 (and enable lock pages in memory). With AWE enabled, will the max server memory option configure just the size of the data cache or the size of all buffer caches (data, processes, sessions, and so on)? Since only the data cache can make use of AWE-mapped memory, if I configure the max server memory to 4GB, will the data cache use only 1GB (the portion mapped by AWE), or can it use this extra 1GB and continue to use or compete with all other memory consumers in the standard address space?
A Max server memory will always limit the entire buffer pool size; however, the only consumer that can make use of AWE-mapped memory is the data cache, nothing else.
So for your first question, even with AWE enabled, max server memory still limits the entire buffer pool, but non-data-cache consumers won't ever use any of the AWE-mapped memory.
For the second question, the data cache will use AWE-mapped memory plus any other memory in the buffer pool that SQL Server determines appropriate; it won't be limited to only AWE memory, it just happens to be the only consumer that can make use of AWE memory. If you're not sure what the /3GB switch does, see
Raymond Chen's blog
Profiling and Performance
Q I have SQL Server 2005 mirrored in production. When I start SQL Server Profiler on the database machine and write the trace data to a file, I see a drastic drop in performance. Why?
A The reason for the performance drop depends on where you're running Profiler. If you're running it interactively on the server machine, then the Profiler UI is consuming both memory and CPU on the server, which will impact performance.
If you're running Profiler interactively on a workstation, then you're moving the event information across the network. This impacts throughput. If this is the same network you're using for mirroring, you'll see an impact there as well. Also, if you're storing the profiler output on a network share, you're moving data across the network and adversely affecting performance.
Probably the best way to mitigate all of this is to run Profiler non-interactively on the server that is running the instance to be profiled and then pipe the output to a local file. You'll still be consuming server resources but this approach generally has the lowest impact. This works much better than the (in-memory) Profiler trace. File trace uses the system memory more efficiently; it has larger buffers and flushes them to disk more effectively. It also doesn't depend on external processes (like SQL Server Profiler).
Finally, trace data is written to a disk file while Profiler is still profiling. The trace file is shared so that other people can see real-time profile data remotely. If you're calling the trace file interactively, that means you've manually invoked Profiler and are viewing output on the screen. Traces can be created programmatically with no visual output, which is why you should run things non-interactively.
You can create a share on top of a local directory and other people can access the files found there-that's typically fine. As mentioned before, you don't want to send trace output to a file on a remote share, especially one that is accessed through the same network pipe used for mirroring.
You should also select just the minimum set of events needed for your investigation. For the trace file location, you should choose the fastest drive on your system (preferably different from the SQL Server database and transaction log drives). If you still experience significant performance degradation, split the events into two or more traces each targeting a different hard drive. Even if the traces target the same hard drive, you'll still benefit from a split due to the fact that each trace has its own set of buffers. See sp_trace_create and all of its relatives in SQL Server Books Online for more.
Q I am trying to install SQL Server on a cluster running Windows Server 2003. Every time I try this, I get an error stating "Setup failed to perform required operations on the cluster nodes." The sqlstp.log simply states: