William (Bill) Vaughn's Musings

INETA sent me to Tulsa Oklahoma this week for the Tulsa Techfest. My experiences with the newly renovated Crown Plaza hotel notwithstanding, it was a nice trip. It seems that the hotel decided to put me on the newly renovated top floor--where the thermostats were miss-wired--all they cold do is cool... and cool...and cool.

I sat through Brad McGehee's SQL DBA talks and learned (or was reminded of) a few tip that I thought I would share. Some of these tips are pretty obvious but it seems not universally applied.

• If your SQL Server Connection is throwing exceptions, check the NIC. As I had suspected for some time those strange "network exceptions" that occur from time to time on isolated machines might very well be due to having the NIC set to "Auto Detect". If a down-wire component (switch, hub, router) is not set to the right (LAN global) speed, the NIC retries can bring down the connection but not deterministically--just often enough to keep you from getting any sleep.
• Remember to defrag your hard drive before installing a new database and pre-allocate the .MDB and .LDB files used by the master and user databases and tempdb.  This means the file system won't have to churn while fetching data.
• Put the user, system and tempdb databases on separate physical volumes.
• Set the Autosize option to stretch the database in fixed amounts, not a percentage (the default). This means 20% of a 5 GB database won't bring your file system to its knees.
• Disable AutoShrink. This helps performance by not constantly checking to see if the DB can be shrunk. Note this might make sense for SQL Express where user filespace is at a premium. However, I recommend (even for SQL Express) that you plan to install a dedicated (albeit cheap) server so this is not an issue.
• Don't use UPDATESTATISTICS and INDEX REBUILD/REORG--they're redundant and could actually hurt performance.
• Never use SA for applications. Create a special account for system administrator work and protect the password(s).

Handling Multi-Select Parameters in Reporting Services Reports

Before I left for my trip to Tulsa, I finished another article for SQL Server mag (or whoever will buy it) on handling multiple selection parameters for Reporting Services. This is a feature implemented late in the RS 2005 cycle and rebuilt for SQL Server 2008 (or it seems so as it works). The way the Report Processor accomplishes this is through use of a WHERE clause IN expression. However, it seems that you're supposed to figure this out on your own. Basically (and there's a lot more detail in the article),

• Create your SELECT query with a WHERE clause IN expression as shown below:

SELECT Col, Col2, Col3
FROM MyTable
WHERE myCriteriaCol IN (@InputListOfValues)

No, this won't work in TSQL as the IN expression does not take a parameter. However, the Report Processor knows what to do. You should also avoid use of the named parameter elsewhere in the WHERE clause. Note that the "choose all" option is handled by the Report Processor-generated UI.

• Next, using the new Report Designer's Report Data window, configure the Parameter to accept multiple selections from the user.
• Program the parameter "Acceptable Values" to be populated with fixed values or values drawn from another query.
• Set the default (if you want to). I don't recommend setting a default value for all parameters as it causes the report to be executed before the user gets to choose what they want to see in the report. No, you can't use <Null> as a default with a multi-select parameter.
• Test the report

When the Report Processor takes off an interprets the report RDL, it substitutes a delimited string ( 'Red', 'Yellow', 'Blue') into the parameter placeholder in the TSQL query. This means everywhere the @InputListOfValues is referenced in my query the parameter value will be inserted. This is not how "normal" parameters are managed in TSQL (which eliminates the possibility of SQL Injection attacks) so there might be a concern for this type of attack. However, consider that the user is not permitted to enter a parameter here--simply chose one, many or all of the options provided.