THIS ISSUE SPONSORED BY
Need the Newest System Table Map Poster?
Ultimate Support Resource for Local User Groups
(below NEWS AND VIEWS)
SPONSOR: FREE BOOK: MICROSTRATEGY BI APPLICATIONS
Some BI companies focus on marketing. MicroStrategy focuses on building world-class technology. That's why over 2,000 MicroStrategy customers rely on our BI applications to remain ahead of their competition. See applications of MicroStrategy's Industrial-Strength Business Intelligence in action, from financial reporting to sales analysis to advanced fraud detection:
October 23, 2003—In this issue:
- Is Your Tempdb Stressed Out?
2. SQL SERVER NEWS AND VIEWS
- Securing the Perimeter
- Results of Previous Instant Poll: Reporting Services Beta 2
- New Instant Poll: Perimeter Security
- SQL Server Opinions Needed
- 2004 Date Announced for SQL Server Magazine Connections
- What's New in SQL Server Magazine: Dimension Writeback
- Hot Thread: RowLock Hint
- Tip: Table Variables vs. Temporary Tables
5. HOT RELEASE (ADVERTISEMENT)
- Test Server Scalability and Product Performance
6. NEW AND IMPROVED
- Let Analysts and Users Access BI Data
- Automatically Generate Database Components
7. CONTACT US
See this section for a list of ways to contact us.
(contributed by Brian Moran, news editor, [email protected])
Most SQL Server customers use tempdb a lot. But you might not realize that heavy use of tempdb can cause resource-allocation contention and result in potentially serious performance problems. I recently ran across a Microsoft Knowledge Base article that describes potential problems with tempdb that I hadn't been unaware of. Coincidentally, this information has helped some of my clients in the past several weeks, and it might be relevant to your environment.
I recently investigated a performance problem that my client and I suspected was related to creating a large number of objects in tempdb. When the client's site was busy, it created tens of thousands of tables in tempdb in a short amount of time. There's nothing inherently wrong with an architecture that relies heavily on the creation of tables in tempdb, but the site showed an increasing number of locks and latches while response time and throughput began to drop. I won't bore you with all the troubleshooting we did, but we eventually stumbled across a Microsoft article that proved to be surprisingly helpful, "FIX: Concurrency Enhancements for the Tempdb Database" ( http://support.microsoft.com/default.aspx?scid=kb;en-us;328551 ).
This article describes how the page-free space, Secondary Global Allocation Map (SGAM), and Index Allocation Map (IAM) pages can become tempdb hotspots when you quickly create many objects in tempdb or delete them from tempdb. Potential problem operations include tempdb activity associated with the following:
- Repeated creation and dropping of temporary tables (local or global)
- Using table variables that use tempdb for storage
- Using work tables associated with cursors
- Using work tables associated with an ORDER BY clause
- Using work tables associated with a GROUP BY clause
- Using work files associated with hash plans
The article offers three solutions for avoiding this potential tempdb bottleneck: a hotfix, a trace flag that reduces mixed-extent allocation for small objects, and a recommendation to increase the number of files in tempdb. Increasing the number of files in tempdb, even if they're all on the same disk, helps minimize contention on the SGAM because each tempdb file has its own SGAM. Microsoft expects to include the hotfix in SQL Server 2000 Service Pack 4 (SP4), but I don't recommend applying it without experimenting with the trace flag and tempdb file-management changes, which are less intrusive. (Covering SGAMs, mixed extents, and other issues is beyond the scope of this commentary. But Kalen Delaney's "Inside SQL Server" column in SQL Server Magazine is an excellent source for this kind of SQL Server internals information.)
I suspect that this tempdb bottleneck is more common than Microsoft realizes. Although I discovered the Microsoft article about the tempdb problems just recently, I've seen this type of problem affect several customers and read multiple newsgroup postings that describe similar symptoms associated with heavy use of tempdb. Without the information in this Knowledge Base article, many customers might have chalked up most of their tuning problems to "ghosts in the machine" and would have had a difficult time troubleshooting—if they could discover the problem at all. If you think tempdb is slowing down your system, check out this article.
NEED THE NEWEST SYSTEM TABLE MAP POSTER?
Become a subscriber to SQL Server Magazine and automatically receive the most recent version of the Microsoft SQL Server 2000 System Table Map Poster--AND as a subscriber, you will receive 12 print issues that give you access to all of the newest information and tools needed for SQL Server. This also includes free access to the entire SQL Server Magazine article archive and active forums on the Web during your subscription term. Limited quantities of the poster are available, so hurry and subscribe today! Click here:
2. SQL SERVER NEWS AND VIEWS
Microsoft CEO Steve Ballmer recently discussed his company's ever-evolving plans to secure its users' systems at the Microsoft Worldwide Partner Conference 2003 in New Orleans. Going forward, Ballmer pledged that Microsoft would reduce the size of patches, reduce the number of reboots that patches cause, introduce better patch-deployment automation, address the needs of legacy systems, provide more predictable patch schedules, and provide more guidance about securely deploying and managing Microsoft systems. That wasn't all Ballmer had to say, however. A growing feeling among security experts at the software giant is that a new strategy of better securing the edges of networks might ultimately better protect the numerous Windows systems found within those networks. This strategy, called "Securing the Perimeter," is now a core tenet of Microsoft's wider Trustworthy Computing initiative, and it will affect all IT administrators and decision makers who use Microsoft products. Read what Paul Thurrott has to say about this perimeter strategy in his Windows and .NET Perspectives column, "Securing the Perimeter," at
The voting has closed in SQL Server Magazine's Instant Poll for thequestion, "Are you interested in participating in the second beta test for SQL Server Reporting Services?" Here are the results (+/- 1 percent) from the 205 votes:
- 35% Yes, I'm already signed up
- 24% Yes, I plan to participate
- 23% Yes, but I don't have time
- 18% No, I'm not interested
Sponsored by EMC
Manage your Microsoft environment with EMC solutions and better utilize your storage/server assets. Click here.
The next Instant Poll question is "Do you think Microsoft's 'Secure the Perimeter' strategy will significantly reduce the company's security problems?" Go to the SQL Server Magazine Web site and vote for 1) Yes, it's a great strategy, 2) No, Microsoft needs to address the underlying security of its products, or 3) I don't know.
SPONSOR: ULTIMATE SUPPORT RESOURCE FOR LOCAL USER GROUPS
SSWUG is a support resource for issues with SQL Server, Oracle, and XML technologies. Find the latest news, tips, security bulletins, and immediate responses to questions at www.SWUGG.org. Don't miss out... visit the site today:
(brought to you by SQL Server Magazine and its partners)
Join the SQL Server Magazine industry research panel today to make your voice heard in the SQL Server Community. Provide market input and comments about trends in the industry. As an added bonus, you'll also get a free SQL Server Magazine hat. The hat supply is limited, so register today!
Save this date on your calendar. Spring 2004 SQL Server Magazine Connections will be held April 18-21 at the Hyatt Grand Cypress in Orlando, Florida. Early registrants will receive free access to concurrently running Microsoft ASP.NET Connections and Visual Studio Connections. For more information, call 203-268-3204 or 800-438-6720.
When you think of analytic applications, you might automatically think of analyzing historical data. However, many analytic applications such as budgeting and planning applications make excellent use of OLAP technology by analyzing future data. In SQL Server 2000, Microsoft added several analytic features that make Analysis Services a better tool than it was in SQL Server 7.0 for these planning applications. Analysis Services lets you allocate a new value to all cube cells that contribute to the target cell. For example, if you enter a forecasted amount for product sales for a given quarter, Analysis Services can allocate that value among all the months in that quarter based on a weighting formula that you specify. And in addition to letting you write back to cube cells, an ability that SQL Server 7.0 provided, SQL Server 2000 lets you modify a dimension's structure. This ability is called dimension writeback. Read all about this new ability in Russ Whitney's October SQL Server Magazine column, "Dimension Writeback," at
Aspiring Geek has run into a simple UPDATE statement on a table that uses the RowLock hint. The stored procedure just finds a row based on the primary key, then updates it. However, the processes pointing at this table have been timing out. The table has about a million rows and, until recently, was a heap. But Aspiring Geek created a clustered index on the primary key (an identity column) and moved the data to a different filegroup from the other two indexes. An asynchronous process makes the updates, and Aspiring Geek suspects the RowLock hint was introduced because the asynchronous processes were stepping on each other. He thinks he should remove the hint. Read what other people have said, and offer your suggestions, on SQL Server Magazine's Administration forum at
(contributed by Brian Moran, [email protected])
Q. In general, is there a performance difference between using table variables and using temporary tables in SQL Server 2000?
A. There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. I took the following tip from the private SQL Server MVP newsgroup and received permission from Microsoft to share it with you. One MVP noticed that although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.
A senior member of the SQL Server development team told me that table variables use internal metadata in a way that prevents the engine from using a table variable within a parallel query. He also said that SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. Without statistics, SQL Server might choose a poor processing plan for a query that contains a table variable. The development team member added that you should limit your use of SQL Server 2000 table variables to reasonably small queries and data sets and use temporary tables for larger data sets.
This advice about table variables contradicts some past Microsoft information, which said that table variables were faster because they're created in memory. However, table variables can incur disk I/O in ways similar to temporary tables.
In deciding whether you should use table variables or temporary tables, benchmark both in your environment. I suspect that temporary tables will provide better performance on larger result sets in most cases. For more information about table variables, see the Microsoft article "INF: Frequently Asked Questions--SQL Server 2000--Table Variables" at http://support.microsoft.com/default.aspx?scid=kb;en-us;305977.
Send technical questions to [email protected].
5. HOT RELEASE (ADVERTISEMENT)
SQL Server Magazine and CSA Research have made available the Database Performance Portal. IT professionals can conduct scalability studies, perform ad hoc systems health analysis, identify infrastructure bottlenecks, conduct off-site diagnostics, and qualify hardware purchases. Inquire today:
6. NEW AND IMPROVED
(contributed by Dawn Cyr, [email protected])
ProClarity announced ProClarity Analytics Platform 5.3, a custom analytics platform that gives analysts and end users 52 components and 2500 APIs that they can use to customize data analysis to fit their organizations' needs. The latest release of the platform includes a new dashboard that lets business analysts customize the data they use. The dashboard provides an activity tracker that lets analysts see who is using the information they provide to their organizations. The platform's updated business logic server lets analysts manage data according to their organizations' business rules and best practices. A new Key Performance Indicator (KPI) Designer includes 19 templates that analysts can use to create KPIs to share with coworkers. And a new thin-client interface lets non-expert users access data they need through an intuitive, browser-based UI. ProClarity Analytics Platform 5.3 works with SQL Server 2000. For pricing and more information, contact ProClarity at 208-344-1630 or [email protected]
eSolutions announced IronWorks, a template-based universal code generator. The product contains 51 core templates that let software developers generate programs and components that interact with database systems. The generator reads a database's schema and lets users set extended properties for tables and columns, then applies templates to each table to generate code. The templates are organized in libraries, one that generates C# code and one that generates Visual Basic .NET code. The templates produce three-tier solution architectures that use stored procedures to access and modify data. Users can create their own templates or edit the core templates for their particular needs. IronWorks supports SQL Server 2000, and you can purchase it online at http://www.componentsource.com . The product costs $535.28 for a single-developer license, $2432.63 for a five-developer license, and $5361.53 for a site license. For more information, contact eSolutions at
7. CONTACT US
Here's how to reach us with your comments and questions:
- ABOUT THE COMMENTARY — [email protected]
- ABOUT THE NEWSLETTER IN GENERAL — [email protected]
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — [email protected]
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — [email protected]
- WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
Contact Richard Resnick at [email protected] or 800-949-4007.
SQL Server Magazine UPDATE is brought to you by SQL Server
Magazine, the only magazine completely devoted to helping developers
and DBAs master new and emerging SQL Server technologies and issues.
Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.