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:http://lists.sqlmag.com/cgi-bin3/DM/y/edK20FgQMn0BRZ0BDLO0AK

October 23, 2003—In this issue:

1. COMMENTARY

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

3. ANNOUNCEMENTS

SQL Server Opinions Needed

2004 Date Announced for SQL Server Magazine Connections

4. RESOURCES

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.

1. COMMENTARY

IS YOUR TEMPDB STRESSED OUT?

(contributed by Brian Moran, news editor, brianm@sqlmag.com)

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:http://lists.sqlmag.com/cgi-bin3/DM/y/edK20FgQMn0BRZ0BDBs0Ak

2. SQL SERVER NEWS AND VIEWS

SECURING THE PERIMETER

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 http://www.winnetmag.com/article/articleid/40590/windows_40590.html

RESULTS OF PREVIOUS INSTANT POLL: REPORTING SERVICES BETA 2

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:

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.http://www.sqlmag.com

3. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

SQL SERVER OPINIONS NEEDED

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! http://lists.sqlmag.com/cgi-bin3/DM/y/edK20FgQMn0BRZ05nA0AJ

2004 DATE ANNOUNCED FOR SQL SERVER MAGAZINE CONNECTIONS

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. http://lists.sqlmag.com/cgi-bin3/DM/y/edK20FgQMn0BRZ0qSH0Ax

4. RESOURCES

WHAT'S NEW IN SQL SERVER MAGAZINE: DIMENSION WRITEBACK

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 http://www.sqlmag.com/articles/index.cfm?articleid=39887

HOT THREAD: ROWLOCK HINT

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 http://www.sqlmag.com/forums/messageview.cfm?catid=3&threadid=20089

TIP: TABLE VARIABLES VS. TEMPORARY TABLES

(contributed by Brian Moran, savvy@sqlmag.com)

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.

6. NEW AND IMPROVED

(contributed by Dawn Cyr, products@sqlmag.com)

LET ANALYSTS AND USERS ACCESS BI DATA

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 sales@proclarity.com. http://lists.sqlmag.com/cgi-bin3/DM/y/edK20FgQMn0BRZ01eO0AK

AUTOMATICALLY GENERATE DATABASE COMPONENTS

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 http://lists.sqlmag.com/cgi-bin3/DM/y/edK20FgQMn0BRZ0BDLP0AL

7. CONTACT US

Here's how to reach us with your comments and questions:

ABOUT THE COMMENTARY — brianm@sqlmag.com

ABOUT THE NEWSLETTER IN GENERAL — kathy@sqlmag.com(please mention the newsletter name in the subject line)

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. Subscribe today.http://www.sqlmag.com/sub.cfm?code=ssei211x1y

Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.http://www.winnetmag.net/email

Discuss this Article 2

Anonymous User (not verified)

on Oct 27, 2004

Does anyone have an opinion on letting users create their own queries on a production server? The database is a copy of what is used by the business application so they aren't directly accessing the production Db.

Does anyone have an opinion on letting users create their own queries on a production server? The database is a copy of what is used by the business application so they aren't directly accessing the production Db.

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More