After a long and much anticipated wait SQL Server 2005 has been released to manufacture (RTM).  Although the official launch of the product is not until the 7th of November the final build of the product was announced on Friday the 29th of October.  For MSDN subscribers the final build of the Developer, Enterprise, Express, Standard and Workgroup Editions are all now available for download.

 

If you are thinking of holding back from deploying SQL Server 2005 in your production environment until Service Pack One is released then below are some statistics from some of the worlds largest companies that have already deployed SQL Server 2005:

 

Barnes & Noble - 3TB Data Warehouse on a 64-Bit machine

Experience - Largest Analysis Service cube with 15million rows and 300 attributes

NASDAQ - 5,000 transactions /second and over 100, 000 queries/day on a trade summary system

Mediterranean Shipping - 50 million transactions/day running their most mission critical shipping application 24x7

Xerox - 7 million transactions /day

 

The SQL Server 2005 launch events kick off in San Francisco on the 7th of November 2005 and will continue in Australia during late November and early December.  To be a part of the launch events you can register at http://www.microsoft.com/australia/readylaunch/itpro.aspx.

 

 

Should you have any suggestions or feedback on how we can improve the newsletter or content that you would like covered, please do not hesitate to email newsletter@wardyit.com.

 

Happy Reading

 

 

Peter Ward

WARDY IT Solutions Chief Technical Architect

Phone: +61 7 3856 5411     Mobile: +61 0 403 177 761

In this Issue:

SQL Server 2000 Performance Analysis Tools

SQL Server beats the TPC $1.00/transaction barrier

Understanding TEMPDB

SchemaSpy

SQL Server vs Oracle

New SQL Server Certifications

Dynamic Management Views

SQL Server Fiber Mode

DMO of the Month

T-SQL of the Month

SQL Server Tip of the Month

 

SQL Server 2000 Performance Analysis Tools

Microsoft has released two more of the tools that have been developed internally for the Product Support Services (PSS) Team to identify performance issues with SQL Server.  read80Trace can be used to process trace files that are generated by SQL Server 2000 in order to produce a summary of the details in the trace file.  The data generated by read80Trace can be stored in a normalised database allowing you can perform your own queries on it.  The OSTRESS utility is a multithreaded ODBC-based query utility that can be used for stress testing SQL Server 2000. You can pass either an RML file produced by read80Trace or a standard go-delimited SQL script file as the query in order to perform a specific type of stress load on the server. http://support.microsoft.com/default.aspx?scid=kb;en-us;887057

 

 

SQL Server beats the TPC $1.00/transaction barrier

The Transaction Processing Performance Council (TPC) has recently published that SQL Server 2005 has broken the $1 price-per-transaction ($/tpmC) barrier. Basically this means that 38,622 transactions per-second were processed under TPC guidelines (http://www.tpc.org/tpcc/detail.asp) on a single Dual-Core Intel® Xeon™ processor 2.80 GHz 2+2M server costing US$38,028.  http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=105092601

 

 

Understanding TEMPDB

Kimberly Tripp (http://www.sqlskills.com/blogs/kimberly) from SQL Skills has written a great article on how TEMPDB is used by SQL Server and how to optimise TEMPDB so that it does not become a bottleneck.  The article also discusses some of the confusion that surrounds the use of Table Variables vs Temp tables identifying when each particular method should be used.

http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=8be9d388-b354-429f-8c98-e45989a4bebe

 

 

SchemaSpy

SchemaSpy is a Java-based tool that analyses the metadata of a schema in a database and generates a visual representation in a browser.  It supports the majority of major DBMS players including SQL Server.  SchemaSpy supports browsing the schema through a graphical representation of the relationships and has an interesting feature called ‘Anomalies’ which identifies configurations that may not be quite right with the schema. http://schemaspy.sourceforge.net/

 

 

SQL Server vs Oracle

There continues to be a slug match between SQL Server and Oracle regarding the performance of each DBMS.    Oracle continues to state that it leads the market in regards to scalability and performance especially in the Very Large Database space.  The following article is an independent comparison of the strengths and weaknesses of both the VLDB and OLTP features of each product. http://www.wisdomforce.com/dweb/resources/docs/MSSQL2005_ORACLE10g_compare.pdf

 

 

New SQL Server Certifications

Recently Microsoft announced a new set of certifications for IT professional working with SQL Server Microsoft Certified Technology Specialist: SQL Server 2005, Microsoft Certified IT Professional: Database Developer, Microsoft Certified IT Professional: Database Administrator and Microsoft Certified IT Professional: Business Intelligence Developer.  The MCDBA for SQL Server 2000 will continues to be recognized by Microsoft and you do not need to do anything to maintain it.  There is an upgrade option for SQL Server 2005 which is available to those currently certified as MCDBA’s.  The upgrade exam will be available early-mid next year which will allow you to upgrade to the new MCITP: Database Administrator credentials after earning the prerequisite Technology Specialist credentials.   For more details regarding what this means to those with existing certifications refer to http://www.microsoft.com/learning/mcp/newgen/faq/

 

 

Dynamic Management Views

Dynamic Management Views (DMVs) have been introduced in SQL Server 2005 to allow you to see what is happening under the covers of SQL Server.  DMV’s have been designed to be used instead of the system tables and the various other functions that were used in SQL Server 2000 to help look under the hood.  For more information on the data that can be obtained using the DMV’s refer to http://msdn2.microsoft.com/en-us/library/ms188754.aspx

 

 

SQL Server Fiber Mode

Recently we have seen several sites were the use of Windows NT fiber mode has been enabled.  Fiber mode can help to reduce context switching between threads and switches between user mode and kernel mode on the CPU.  However, it is generally recommended that fiber mode is not enabled.  Ken Henderson has written a great article which explains the reason that fiber mode isn't generally recommended.  http://msdn.microsoft.com/library/?url=/library/en-us/dnsqldev/html/sqldev_02152005.asp

 

 

 

DMO of the Month:

 

Often there is the need to use a SELECT or INSERT column list for a table that has a long list of columns.  Rather then copying each of the columns and then adding a comma between each column the VBScript code snippet below can be used to generate a column list that can simply be copied and pasted into Query Analyzer.

 

Option Explicit

 

Const OUTPUT = "c:\output.txt"

Const ForWriting = 2

 

Dim oSQLServer

Dim oFSO

Dim oFSOWrite

Dim oShell

 

Dim col

Dim line

Dim ServerName

Dim Database

DIm Table

 

Set oSQLServer = CreateObject("SQLDMO.SQLServer")

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oShell = CreateObject("WScript.Shell")

 

Set oFSOWrite = oFSO.OpenTextFile(OUTPUT, ForWriting, True)

 

' Additional validation should be added to this input

ServerName = InputBox("Enter the SQL Server instance to connect to", "www.wardyit.com")

Database = InputBox("Enter the Database", "www.wardyit.com")

Table = InputBox("Enter the Table to generate a column list for", "www.wardyit.com")

 

oSQLServer.LoginSecure = True

oSQLServer.Connect ServerName

 

For Each col In oSQLServer.Databases(Database).Tables(Table).Columns

      line = Line & col.name & ", "

Next

 

line = mid(line, 1, instrrev(line, ",") - 1)

 

oFSOWrite.Write line

 

oFSOWrite.Close

 

' For details regarding the Windows Script Host run method refer to

' http://shrinkster.com/91z

oShell.Run "notepad.exe " & OUTPUT, 3

 

oSQLServer.DisConnect

 

Set oShell = Nothing

Set oFSO = Nothing

Set oSQLServer = Nothing

 

 

T-SQL of the Month:

 

A column list as demonstrated in the DMO of the Month is also able to be generated using a T-SQL Solution.  The T-SQL code below will perform a concatenation of the columns of a table into a single row.  For additional details regarding the concatenation of columns into a single row including how to return more then one list then refer to the following article http://www.aspfaq.com/show.asp?id=2529.

 

SET NOCOUNT ON

 

DECLARE @row varchar(8000)

SET @row = ''

 

SELECT     @row = COALESCE(@row + column_name + ', ', '')

FROM  INFORMATION_SCHEMA.COLUMNS

WHERE      table_name = 'orders'

 

SELECT SUBSTRING(@row, 1, LEN(@row) - 1) AS ColumnList

 

 

SQL Server Tip of the Month

 

With more and more organizations starting to deploy the 64-Bit version of SQL Server 2000 there is a growing need to be able to identify which version is running on a particular SQL Server 2000 instance.  The easiest way to identify the version is to use the @@VERSION configuration function:

 

ie.

 

SELECT @@VERSION

 

If the 32-bit version of SQL Server 2000 is installed the following will be returned:

 

Microsoft SQL Server 2000 - BuildNumber (Intel X86)

 

However if the version is the 64-bit version the following will be returned:

 

Microsoft SQL Server 2000 - BuildNumber (Intel IA-64)

If you are unable to view this document it can be downloaded from http://www.wardyit.com/newsletters/oct2005.htm

WARDY IT Solutions is a highly regarded IT Solutions provider specialising in Microsoft SQL Server.  You may not be aware though that WARDY IT Solutions is also a registered Microsoft Partner and has an established relationship with numerous hardware and software vendors, enabling us to provide competitive pricing on your next hardware or software acquisition.  For more information on how WARDY IT Solutions can assist with your next acquisition email: contact@wardyit.com.  WARDY IT Solutions has also recently partnered with Web Central in order to add web hosting and DSL plans backed by guaranteed Service Levels to the extensive list of services that we already provide.

You are receiving this monthly newsletter as a valuable associate of WARDY IT Solutions. Should you wish to unsubscribe then send a blank e-mail to newsletter_remove@wardyit.com. Alternatively if you have been forwarded this newsletter and would like to subscribe than send a blank email to newsletter_add@wardyit.com.