Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Jeff Garbus at jeff@soaringeagle.biz
Q: I am interested in a referenece for the "30 - less than 30% of the performance problems can be found by load testing". By any chance, is this Fluke Networks? Can you share that reference?
A: This was an internal number from a load testing company, I am under nondisclosure as to the name.
Q: For 3rd Party dbs where creating an indexed view is almost impossible because the db/tables are not designed to support indexed views, what measures do you recommend?
A: Index the underlying tables
Q: We can use Extended Events starting from SQL Server 2008 as a replacement of SQL Profiler 
A: Good call!
Q: Can you review page life expencency rule of thumb again?
A: Hundreds is bad, thousands or tens of thousands is good
Q: Would it be substantially feasible to have guests of a host to be dedicated for SQLSERVER rather than for these VMs to be mixed, that is, some for IIS, some for SQLSERVER...etc
A: Yes, put SQL Server on its own VM
Q: Is partitioning good for tuning? When is the use case for it?
A: Yes, as tables get big enough that you find you're using a low percentage of the data, you can backup or perform work on a subset of the data, even moving some data to a different (slower? Faster? Cheaper?) drive
Q: Do you mind recommending some perfmon counters for monitoring in macro level for I/O, network, CPU, memory etc.?
A: Disk queue length for I/O, the others I use tools for because I don't like small snapshots of data
Q: How do you find objects that are under lock wait states?
A: sp_lock / sp_who
Q: When would you use SQL profiler if you have Ignite?
A: Only if I needed to complete an audit of everything coming into the server (ignite samples 1/second, so it's possible things are missed)
Q: The real issue is DBAs spend a lot of time doing mundane tasks such as backing things up, creating batches but little time fine tuning and learning about performance techniques.
A: Yes, absolutely! You must automate mundane tasks, and only alert when there's an exception.
Q: What is the blog site of the speaker?
A: http://mssqlperformance.blogspot.com/
Q: What is the difference between with recompile and option (recompile)
A: One goes into the proc, the other is a one-off for a proc execution
Q: In the case of NAS, DQ spike may be the sign of flooded network because the NAS use shared network
A: Yes
Q: Typical install of sql has many disks. Can the graph display this per disk ?
A: No; but please request this of Confio, it's a GREAT idea for an add-on (I may have mentioned this once or twice; enough folks ask for it, we'll get it)
Q: Blocking solutions: 1) optimze queries, 2) Covered indexes, 3) Decrease isolation levels, 4) Partition contended data. Also, create a SQL Agent Job to collect and detect blocking.
A: 2) Not sure that one's a blocking solution 5) consider snapshot isolation
Q: About VLFs, it is best to shrink the transaction log file to its minimum, and then grow the transaction log file by chunks of 1GB, so as to have not too few but not to many VLFs (1GB created 16VLFs)
A: Good point.
Q: I have 245 virtual logs. How did you say I can correct this? I do 15 minute tlog backups.
A: Right after a backup, shrink the log to 2 meg. Then make it bigger all at once.
Q: What about inconsistent performance problems ramdomly showing each time on different clients?
A: Update your stats more often, perhaps with a higher sampling rate
Q: I usually look at CXPACKET waits for adjusting MAX DOP.
A: That's another good indicator
Q: How often does the client have baseline data? Is there a base set of baseline data you recommend all customers capture?
A: Yes, lots of it, that's why I like a tool that gathers it over time & tracks it.
Q: Does adjusting the Cost Threshold for Paralellism makes sense when adjusting MAXDOP?
A: I don't usually mess with the cost threshold, but that doesn't mean it's not appropriate in many cases.
Q: Do these stats need trace to enable?
A: No
Q: I little more information on Parrallelism: is there set level to set for a database server?
A: I usually let it default to "max" unless I see a specific reason to decrease it. When I adjust it, it's usually to something less than the number of processors, I adjust & observe.
Q: What is the recommended limitation of databases to mirror in SQL before performance is affected?
A: Not aware of a numerical limit, keep going until youre resources hit acceptable limits.
Q: Is the amount of VLFs dependant on the number of data files?
A: No, upon the number of times the log is grown (usually autogrown)
