BradC's questions

I have a 64-bit Windows 2003 server with 48 CPU cores and 128GB of RAM running a single application (SQL 2008 Analysis Services). SSAS is currently using about 50GB of memory.

SSAS relies to a large extent on the Windows File Cache to hold frequently used data (see this article, for example). On my server, the windows file cache is normally in the 10-15GB range, but occasionally it will suddenly drop to 5-6GB (Memory\System Cache Resident Bytes). When this occurs, all the SSAS page reads have to go to disk, and queries start timing out until the file cache repopulates.

I have a second server (only 24 cores, but otherwise near-identical) that doesn't exhibit the same symptoms, even running the exact same SSAS instance, the same queries, and the same load (this is a load-balanced environment).

I've asked a detailed SSAS-focused question on dba.stackexchange.com, but a few questions about the Windows behavior:

1. Is there a way to know why the SSAS database file is getting flushed out of the cache?

2. Can I pre-populate the file cache or actively manage it in some way? (I'm not sure that using a RAMDRIVE is an option in our environment)

At one point, we did change the network optimization setting on the server to "Maximize data throughput for file sharing" to match the other box, but that hasn't seemed to make a significant difference.

Edit: Added bounty. If we can't answer "why", then perhaps just a way to better understand what processes are currently using the cache, or what files are in it, or something that might lead us in the right direction.

I'm working on implementing Paul Randal's method of manually spreading DBCC CHECKDB over several days from his excellent article CHECKDB From Every Angle: Consistency Checking Options for a VLDB. In short, the strategy consists of:

• Divide the tables in the database equally between 7 buckets (using number of pages)
• Run a DBCC CHECKALLOC twice a week
• Run a DBCC CHECKCATALOG once a week
• Run a DBCC CHECKTABLE on one bucket each day of the week

I've got a separate StackOverflow question regarding a good algorithm to divide the tables into buckets (chime in there if you have a working TSQL script), but this question is about making sure I'm checking the right things. The Books Online documentation for CHECKDB says that in addition to doing the CHECKALLOC, CHECKCATALOG and CHECKTABLE, it also:

• Validates the contents of every indexed view in the database.
• Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM. (SQL 2008 only)
• Validates the Service Broker data in the database.

So here are my questions:

1. Are there ways to perform these additional checks separately? Should I even worry about it? (Indexed views are probably a more immediate concern to me than the other two)

2. Any significant alterations I need to make to my strategy if I want to implement this across SQL2000, 2005, and 2008 databases?

3. CHECKALLOC and CHECKCATALOG seem to run pretty quickly. Any reason not to run these 2 checks every day?

Thanks!

Assuming I can't put everything on RAID10, which parts of SQL (Data files, log files, TempDB, Backups, etc) benefit the most from being on RAID 10 instead of RAID5?

Does it depend on the type of database (OLTP vs Reporting vs Analysis)?

What kind of actual performance increase should I see going from RAID5 to RAID10? 10%? 50%? 200%??

Technical details: New HP XP24000 disk array, using 300GB 15k drives

We have to make some decisions up front about how much will be provisioned as RAID5 (28x4) vs RAID10 (4+4). This is a one-time provisioning, and can't be changed later, so we're trying to find the right balance of cost/GB vs performance benefit.

We have a mix of SQL 2000, 2005, and 2008 servers, and we've always run a DBCC CHECKDB nightly just before the full backups, under the theory that you want to make sure the DB is in good shape before you back it up. (Obviously, full verification of backups can only be done via test restores, but thats a slightly different topic.)

Assuming that I can't offload the DBCCs to a backup server or something (which would be ideal), is DBCC CHECKDB followed by FULL BACKUP the best sequence?

The only "best practices" document I found that discuss this was a Best Practices for SQL Server Maintenance for SAP from 2006 I found on TechNet:

Ideally, a consistency check using DBCC CHECKDB sould be run before performing an online database backup.

Is this advice correct? Is it correct for all versions of SQL?

(In case this helps, part of the motivation for asking this is that the DBCC runtime seems to vary a fair amount from night to night, so we can't rely on exactly when the backups will be complete, which makes scheduling our tape archive jobs difficult. Also, if maintenance runs long and has to be cancelled for any reason, I'd much rather that the backup be reliably complete than the DBCC.)

(Posted first on SO, but its not getting any answers there. Cross-posting to reach a larger audience, since it is cross-discipline.)

In the process of trying to help out an app dev team with some performance issues on a SQL 2000 server, I ran a SQL trace and discovered that all calls to the database are full of API Server Cursor statements (sp_cursorprepexec, sp_cursorfetch, sp_cursorclose).

Looks like they're specifying some connection string properties that force the use of server-side cursors, retrieving only 128 rows of data at a time: (From http://msdn.microsoft.com/en-us/library/Aa172588)

When the API cursor attributes or properties are set to anything other than their defaults, the OLE DB provider for SQL Server and the SQL Server ODBC driver use API server cursors instead of default result sets. Each call to an API function that fetches rows generates a roundtrip to the server to fetch the rows from the API server cursor.

UPDATE: They have multiple Java apps using a JDBC connection to the SQL server with selectMethod=cursor specified (as opposed to selectMethod=direct).

From my DBA perspective, that's just annoying (it clutters the trace up with useless junk), and in all likelihood is resulting in many extra app-to-SQL server round trips, reducing overall performance.

They've apparently tested selectMethod=direct in a very limited way (changed just one application out of ~60) and experienced some issues of some sort (technical details of which I do not have).

So, my questions are:

• What's the performance impact of selectMethod=cursor vs direct on an already busy SQL 2000 server? (I had speculated that increasing the number of round trips between SQL and APP servers can't do anything good. Was I wrong?)
• Is selectMethod an application-transparent setting? Could this break their apps if we change it?
• When should they use cursor vs direct? Are there some types of apps that would benefit from one over the other?

Update: Found out the name of the driver parameter, which have warranted significant edits to title, body, and tags.

Update: Added bounty. Also added bounty to SO question (which is more focused on application behavior). Thanks!

Right now I'm using SQL Server Management Studio 2005 to manage all my SQL servers (2000, 2005, 2008). I'd love to be able to take advantage of all the cool new features in SSMS 2008, the only thing holding me back is the new DMV-based activity monitor:

• We are still 40% SQL 2000, which doesn't have the required DMVs for the new monitors
• I don't like it as much as the old one :)

So has anyone made a plugin for SSMS 2008 that emulates the old activity monitor? If not, what about a slick set of scripts that accomplish the same thing or better, and work across 2000-2008 (more than just sp_who2)?

I have a SQL 2000 server with a DTS package that is crashing with insufficient memory errors, where we've never had an issue before.

The DTS package is returning the error "There is insufficient system memory to run this query." The SQL error log shows stuff like: (trimmed duplicate lines for simplicity)

BPool::Map: no remappable address found.
Buffer Distribution:  Stolen=198908 Free=779 Procedures=349 
                      Inram=0 Dirty=10291 Kept=0
Buffer Counts:  Commited=917376 Target=917376 Hashed=717340 
                InternalReservation=174 ExternalReservation=24 
                Min Free=1024 Visible= 199856
Procedure Cache:  TotalProcs=17 TotalPages=349 InUsePages=340
Dynamic Memory Manager:  Stolen=164278 OS Reserved=1048 
                OS Committed=1026 OS In Use=1024
                Query Plan=416 Optimizer=141466
                General=16874
Buffer Counts:  Commited=917376 Target=917376 Hashed=717340
                Utilities=5840 Connection=206
                InternalReservation=174 ExternalReservation=24 
                Min Free=1024 Visible= 199856
Procedure Cache:  TotalProcs=17 TotalPages=349 InUsePages=340
                Utilities=5840 Connection=206
Global Memory Objects:  Resource=1272 Locks=279 
                SQLCache=52 Replication=2
                LockBytes=2 ServerGlobal=23
                Xact=35
Query Memory Manager:  Grants=1 Waiting=0 Maximum=35238 Available=270

Can someone help me interpret/decipher some of these memory numbers?

This is a dedicated Windows2003 server running SQL2000 Enterprise edition, build 8.00.2282 (SP4). It has a total of 8GB of RAM. The SQL instance is configured with Min Memory = 0, Max Memory = 7167. AWE is enabled.

I've found a ton of articles that seem kinda related:

• KB 838459 - But we're already on SP4, and this isn't a reindex.
• KB 815114 - Seems relevant, as our query does have a LOT of tables in the join, more than half of them with a LEFT OUTER, but as I said, we're already on SP4.
• KB 831999 - Ditto. Already on SP4.

I admit this is a big hairy query, but we've run the identical query for years without trouble, and even if the query isn't really optimal, it shouldn't crash the server, or fail to execute, right?

Any ideas? Should we try the trace flag 3940 mentioned in kb838459, even though the scenario isn't exactly the same?

Yes, we've been encouraging them to upgrade to SQL2008 64-bit, but that's a while off.

Following up on my original StackOverFlow question, we're trying to create a SQL user (on many 2000, 2005, and 2008 servers) with the minimum possible permissions for doing a simple "heartbeat check" to make sure the instance is still alive. We'd settled on the script executing a simple "SELECT @@VERSION".

In the name of "Minimum allowed permission" we'd like to prevent this user from doing (nearly) anything else, though. Unfortunately, even without adding any explicit permissions, the PUBLIC role (that all users are members of) can do quite a bit. Specifically:

SELECT * FROM SYSDATABASES
Ok, I can explicitly add the user to the db_denydatareader role in the MASTER database to prevent the reading of any system tables

SP_WHO
Ok, I can explicity DENY ALL ON SP_WHO, but what about SP_WHO2 and all the other system stored procs? Do I have to explicity deny all of these?

So since users can't be removed from the PUBLIC role, any other way to prevent the running of system stored procs rather than an explicit deny on each and every one?

Are the system stored procs even worth worrying about? Anything besides sp_who that I really shouldn't want this user to be able to see?

Trying to figure out how to make sure a specific Windows Perfmon Counter Log resumes logging automatically when the server is rebooted.

This is a Windows 2003 Server, so likely won't have anyone logging on to it immediately upon reboot. I saw this similar question, but didn't think that Scheduled Tasks execute without someone logging on...

Edit: Ok, looks like Scheduled Tasks can be set to run a command each server start, but a couple of remaining questions:

• On the "Schedule" tab, should I leave both the "Start log" and "Stop Log" to "manual"? Then use the LOGMAN START command?
• I saw some other web references to instead setting the "Stop Log" to 1 day, and checking the "When a log file closes: Start a new log file" checkbox. Any benefits to this approach? Does it recognize and survive a reboot without the explicit LOGMAN START command?
• The task scheduler UI, on the Settings tab, has a box marked "Stop the task if it runs for..." that defaults to 72 hours. If I don't remove this check, will it stop my log once the server is up for 72 hrs?

And a final strategy question: If I want to monitor a handful of servers in this manner, for a short period of time, am I better off setting these monitors up directly on each server, or to run directly on my workstation, gathering data remotely?

I have a SPID on SQL 2005 that shows in Activity Monitor as "ROLLBACK" mode (because a transaction log filled up, not because it was manually killed). I tried to see how much time it has left to roll back with a

KILL 115 WITH STATUSONLY

but it just said "Status report cannot be obtained. Rollback operation for Process ID 115 is not in progress."

Can I safely issue a "KILL 115" so that I can see the rollback status? Does this actually do anything on a spid currently in rollback?

On some of my SQL2000 servers, I have occasional blocking and weird conflicts between our full backup and log backup jobs. Based on The answers to this SF question, I need to figure out how to prevent my log file backups from running while my full backups are running on the same database. (Sounds like this was changed in SQL2005, so shouldn't be a problem for them.)

Two strategies occur to me:

1. Before executing the entire log backup job, query the MSDB job history tables to see if the backup step (step 3 of 5) of our nightly full backup job is currently executing. If it is, skip the log backup entirely for all databases.

2. Inside the log backup job script, before running a log backup on an individual database, query the sysprocess tables (or something) to see if a LIGHTSPEED BACKUP is already occurring for that specific database. If it is, skip the log backup on that one database only.

The first one would be much easier to implement, but would end up skipping lots of databases it probably could back up without any issues (possibly allowing the logs to grow too much). The second one is more appealing, but I'm not sure exactly how to determine whether a backup is in process for a specific database.

Plus, with the second option there is a slight chance that the FULL backup could start to backup a database that the log backup is already working on, unless I add the same kind of "check code" to the full backup script. (Obviously, I'd want the full to have priority over the log backup job)

Anyone have an existing SQL 2000 script that already does this? Which approach makes the most sense to consider? Or are there other alternatives?

Is there a way to start a SQL 2000 instance without loading one specific database? We've got a database that is crashing the server at a certain point during the db recovery process, and we're going to try to recover from backup. But we can't start the restore when the instance keeps restarting itself.

(If you're curious, a huge table re-indexing that went bad. It was cancelled, and tried to roll back, but for some reason the SQL service restarted itself during the rollback. Now we're in a situation where the service restarts, it tries to recover the db, then somewhere in the middle it gets stuck and restarts the service again.)

Any help or ideas are appreciated.

We've been asked to recover a specific SQL 2005 job from how it existed at a certain time in the past. We have MSDB database backups from that time period, but I can't overwrite the current MSDB database, because I don't want to lose more recent changes to other jobs on that server.

If I restore it as a copy (call it MSDB_old or something), how can I extract the information for that one job? Or would I need to restore it over the MSDB database on a scratch server?

Lots of articles out there (see Slava Oks's original SQL 2000 article and Kevin Kline's SQL 2005 update) recommend disabling hyperthreading on SQL servers, or at least testing your specific workload before enabling it on your servers.

This issue is gradually becoming less relevant as true multi-core processors replace hyperthreaded ones, but what's the current wisdom on this issue? Does this advice change any with SQL 2005 64-bit, or SQL 2008, or Windows Server 2008?

Ideally, this should be tested in advance in a staging environment, but what about for servers that have already made it into production with HT enabled? How can I tell if performance issues we're experiencing might be related to HT? Is there some specific combination of perfmon counters that might point me in that direction, as opposed to all the other things I normally pursue when working on improving SQL performance?

Edit: This is especially attractive because of the potential for an across the board improvement for some of my high-cpu servers, but the client is going to want to see something concrete that helps me identify which servers really could benefit from disabling hyperthreading. Of course, conventional performance troubleshooting is ongoing, but sometimes any little bit helps.

Does restoring a SQL database from backup rebuild its tables and indexes from scratch? Or does it keep it in the same internal physical order it was in at the time of backup?

We're using SQL 2000 with Quest Lightspeed compressed backup, if that makes any difference.

I know that there are really three kinds of fragmentation that I need to be concerned about as a DBA:

1. Index Fragmentation in the SQL data files, including clustered index (table) fragmentation. Identify this using DBCC SHOWCONTIG (in SQL 2000) or sys.dm_ db_ index_ physical_ stats (in 2005+).

2. VLF Fragmentation inside of SQL Log files. Run DBCC LOGINFO to see how many VLFs are in each of your SQL log files.

3. Physical file fragmentation of the database files on the hard drive. Diagnose this by using the "Disk Defragmenter" utility in Windows. (inspired by this excellent blog post)

A lot of attention is paid to index fragmentation (see this excellent Serverfault answer from Paul Randall), so that's not the focus of my question.

I know I can prevent physical fragmentation (and VLF fragmentation) when the database is originally created by planning a reasonable expected data file and log size, because this fragmentation occurs most often from frequent grows and shrinks, but I have some questions about how to fix physical fragmentation once it is identified:

• First of all, is physical fragmentation even relevant on an Enterprise SAN? Can I/should I use Windows Defragmenter on a SAN drive, or should the SAN team be using internal defragmenting utilities? Is the fragmentation analysis I get from the Windows tool even accurate when run on a SAN drive?

• How big of a deal is physical fragmentation on SQL performance? (Let's assume an internal drive array, pending the outcome of the prior question.) Is it a BIGGER deal than internal index fragmentation? Or is it really the same kind of problem (the drive having to do random reads instead of sequential reads)

• Is defragmenting (or rebuilding) indexes a waste of time if the drive is physically fragmented? Do I need to fix the one before I address the other?

• What's the best way to fix physical file fragmentation on a production SQL box? I know I can turn off SQL services and run Windows Defrag, but I also heard about a technique where you do a full backup, drop the database, then restore from the backup to an empty drive. Is this latter technique recommended? Does restoring from a backup like this also build indexes from scratch, eliminating internal index fragmentation? Or does it simply return the page order to the same as when the backup was taken? (We're using Quest Lightspeed backups with compression, if that matters.)

UPDATE: Good answers so far on whether to defragment SAN drives (NO) and whether index defragmentation is still worthwhile on physically fragmented drives (YES).

Anyone else care to weigh in on the best methods for actually doing the defragmentation? Or an estimate on the length of time you'd expect it would take to defrag a large fragmented drive, say 500GB or so? Relevant, obviously, because that's the time my SQL server will be down!

Also, if anyone has any anecdotal info on SQL performance improvements you've made by fixing physical fragmentation, that would be great, too. Mike's blog post talks about uncovering the problem, but isn't specific about what kind of improvement it made.

We have a production website that is experiencing periodic connection errors to our SQL 2005 server, several times a day during peak usage:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

We're certainly investigating other avenues, but so far we haven't seen anything out of the ordinary on the SQL side. We're wondering whether this might be a Named Pipes issue, or whether we'd see the same thing if we forced the web server to use TCP/IP instead. So my questions are:

• Anyone seen a problem like this? Most of the searching I do on this error is for people who can't talk to their SQL server at all because the surface area configuration is messed up. That's not our situation.
• What's the difference between the two? Do they do name resolution differently? These servers are not domain members, they are isolated in their own DMZs, if that changes anything.
• The internet team set up a SQL alias on the web server: "mySQLserverName - tcp/ip - xxx.xx.x.xx, 1433". Would this only be used for TCP/IP resolution, and not named pipes? Could this be part of the problem?
• If I do want to force TCP/IP instead of named pipes, what's the recommended way to do it? This Microsoft KB says I can do it by modifying the connection string. This MSDN forum thread says I can modify the "preferred order" of the native client configuration protocols. I suppose I could also disable Named Pipes on the SQL server altogether, but that seems a bit drastic, and probably not something to try on a production box.

I have a nightly sp_updatestats on a production database, which seems to work fine, except for one particular stored proc that experience performance issues after the update.

The dev team is currently working on fixing the proc (some new indexes and some reorganization of the query that I've recommended), but currently the only workaround is to do an

UPDATE STATISTICS [SuperGiantTable]
WITH FULLSCAN

So, the question is, can I force the sp_ updatestats to do the "with fullscan" option? Do I want to? If not, then I'll probably just add this above UPDATE STATISTICS command to run immediately after the sp_updatestats.

SQL 2000, btw.

Once upon a time, I built my own SQL servers, and had control over drive configuration, RAID levels, etc. The traditional advice of separation of data, logs, tempdb, backups, (depending on budget!) was always a pretty important part of the SQL server design process.

Now with an enterprise-level SAN, I just request a specific amount of drive space for a new SQL server, divided into logical drives for data, backups, and fileshares. Certainly makes my job easier, but there is a part of me that doesn't feel completely comfortable that I can't really peek "behind the curtain" to see what is really going on back there.

My understanding is that the SAN team doesn't configure different "types" of drives any differently (optimizing data drives for random access vs log drives for streaming writes). Some of this may depend on the SAN product itself (we have an HP XP12000 and an HP XP24000), but I've been assured that the HP software does all sorts of dynamic performance configuration (watching for IO hotspots and reconfiguring on the fly to optimize those LUNs), so that the app teams and DBAs don't need to worry about any of that stuff. Something about "spreading the load of all the servers over a huge number of spindles" or something like that.

My questions/discussion:

1. Without making enemies on the SAN team, how can I reassure myself and the application developers that our SQL servers aren't suffering from poorly configured storage? Just use perfmon stats? Other benchmarks like sqlio?

2. If I load test on these SAN drives, does that really give me a reliable, repeatable measure of what I will see when we go live? (assuming that the SAN software might "dynamically configure" differently at different points in time.)

3. Does heavy IO in one part of the SAN (say the Exchange server) impact my SQL servers? (assuming they aren't giving dedicated disks to each server, which I've been told they are not)

4. Would requesting separating logical drives for different functions logical drives (data vs log vs tempdb) help here? Would the SAN see the different IO activity on these and optimally configure them differently?

5. We're in a bit of a space crunch right now. Application teams being told to trim data archives, etc. Would space concerns cause the SAN team to make different decisions on how they configure internal storage (RAID levels, etc) that could impact my server's performance?

Thanks for your thoughts (similar topic briefly discussed in this SF question)