OS Characteristics

Congratulations to Pierre LaFromboise, a data-warehouse manager for the St. Louis Post-Dispatch, Pulitzer Inc. in St. Louis, Missouri, and Vadim Rapp in Park Ridge, Illinois. Pierre won first prize of $100 for the best solution to the April Reader Challenge, "OS Characteristics." Vadim won second prize of $50. Here’s a recap of the problem and the solution to the April Reader Challenge.

Problem:

Mike, a test architect for a company that develops applications using SQL Server as the back-end database, wants to determine which OS version and Service Pack is installed on the machine running the SQL Server service. Mike wants to use T-SQL code to perform this check so that he can use the logic in his stored procedures. All his servers run Windows Server 2003 and the virtual machines he uses for testing purposes run Windows XP Professional Edition. Help Mike write the T-SQL code to determine the following OS characteristics:1. OS version, such as Windows Server 2003 or XP Pro2. OS edition, such as Enterprise Edition or Standard Edition3. Service Pack version

Solution:

Mike can use the netsh OS utility from the T-SQL code to get the OS information that he needs. The netsh utility has several commands that can display or change network settings, for example, the diag command will display various diagnostic information about the computer, OS, network adapter, modem, mail, news, and proxy-client configuration. Mike can execute the diag command using the xp_cmdshell system extended stored procedure, which lets him use documented commands to get the information about the OS. The diag show os/p netsh command will return information about the OS—the /p option shows the OS as properties and values. The output contains the property and value separated by an equals (=) sign. The following T-SQL batch shows how to dump the information from netsh into a temporary table.

CREATE TABLE #os (    token nvarchar(100) NULL)INSERT INTO #os (token)EXEC master..xp_cmdshell 'netsh diag SHOW os /p'SELECT token  FROM #osDROP TABLE #os

The property and value for each field in the output can be parsed using string functions in T-SQL. The following T-SQL batch shows how to retrieve the individual values:

CREATE TABLE #os (    token nvarchar(100) NULL)INSERT INTO #os (token)EXEC master..xp_cmdshell 'netsh diag SHOW os /p'SELECT ltrim(substring(token, 1, charindex('=', token)-1)) AS property,       substring(token, charindex('=', token)+1, 100) AS value  FROM #os WHERE token LIKE '%=%'DROP TABLE #os

Mike can also use the WMI command-line utility (wmic.exe) to get the same details or use the xp_regread extended stored procedure to read the values from the system registry. Both these methods require SQL Server’s service account to run with administrative privileges. Additionally, SQL Server has modified the extended stored procedure xp_regread in the upcoming service pack of SQL Server 2000 to restrict, by default, access to only registry locations created and maintained by SQL Server.

MAY READER CHALLENGE:

Now, test your SQL Server savvy in the May Reader Challenge, "Query Performance On Partitioned Tables" (below). Submit your solution in an email message to [email protected] by April 21. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

David is a database developer for a company that reports about Web site traffic. The information from the Web server log files is stored in SQL Server 2000 databases. One of the main tables in the database is stored as partitions and each partition contains one week’s worth of Web server log file data. David has scripts that bulk insert data into the partitions daily. An example of the partitioned tables follows:

USE tempdbGOCREATE TABLE WebHits_W1 ( WebHitId int NOT NULL PRIMARY KEY, WeekStartId smallint NOT NULL CHECK (WeekStartId = 1) )CREATE TABLE WebHits_W2 ( WebHitId int NOT NULL PRIMARY KEY, WeekStartId smallint NOT NULL CHECK (WeekStartId = 2) )GOCREATE VIEW WebHits_WeeklyASSELECT WebHitId, WeekStartId FROM WebHits_W1UNION ALLSELECT WebHitId, WeekStartId FROM WebHits_W2GO

A sample file that contains the data for the WebHits_W1 table follows:

-- File: c:tempwebhits.txt1,12,13,14,25,26,2

The bulk insert statement that David uses to insert the data into the WebHits_W1 table follows:

BULK INSERT tempdb..WebHits_W1 FROM 'c:tempwebhits.txt'WITH (tablock, datafiletype = 'char', fieldterminator = ',', firstrow = 1, lastrow = 3)BULK INSERT tempdb..WebHits_W2 FROM 'c:tempwebhits.txt'WITH (tablock, datafiletype = 'char', fieldterminator = ',', firstrow = 4, lastrow = 6)

A bug in the bulk insert script caused some of the partitions to be modified without checking the constraints, which resulted in poor query performance. Here are some example queries that are performing poorly after the bulk inserts:

SELECT *FROM WebHits_WeeklyWHERE WeekStartId = 1SELECT top 1 *FROM WebHits_WeeklyWHERE WeekStartId = 1ORDER BY WebHitIdSELECT count(*)FROM WebHits_WeeklyWHERE WeekStartId = 1

Help David identify the performance problem’s cause, query to identify the affected partitioned tables, and correct the problem.