100 posts from: MS SQL Server Development Customer Advisory Team
Optimizing CREATE SET in Cube Calculation Scripts
(Indexed 2010-08-31):
Author: Thomas Kejser Reviewers: Peter Adshead, Nicholas Dritsas, Sanjay Nayyar, John Desch, Kevin Cox, Akshai Mirchandani, Anne Zorner In this blog we will describe an important optimization that you should apply to cubes that are processed often and have CREATE SET commands in the calculation script. We will describe the measurements you can make to determine if this may affect you and also provide solutions that can make your cube be much more responsive to users. Background When you pr...(truncated)...
New Release of SQL Server Migration Assistant (SSMA) includes migration from MySQL and to SQL Azure
(Indexed 2010-08-13):
Wanted to let the community know the SQL Server Product Group just released version 4.2 of the SQL Server Migration Assistant! With this version, Microsoft has announced the release of the first version of SQL Server Migration Assistant (SSMA) for MySQL! Microsoft refreshed the existing SSMA family of products for Oracle, Sybase and Access. Brand new with this release, SSMA for MySQL and SSMA for Access products also support simple and direct migration to SQL Azure.For further details about th...(truncated)...
Loading data to SQL Azure the fast way
(Indexed 2010-07-31):
IntroductionNow that you have your database set up in SQL Azure, the next step is to load your data to this database. Your data could exist in various sources; valid sources include SQL Server, Oracle, Excel, Access, flat files and others. Your data could exist in various locations. A location might be a data center, behind a corporate firewall, on a home network, or even in Windows Azure. There are various data migration tools available, such as the SQL Server BCP Utility, SQL Server Integratio...(truncated)...
Identifying query compile/runtime parameter values using XML SHOWPLAN output.
(Indexed 2010-06-09):
I recently returned from Convergence 2010; the annual North American Microsoft Dynamics User Conference in Atlanta. It was a really great conference with many Customer, Partner, and Microsoft presentations and success stories. It was inspiring to see what Microsoft Dynamics products are being used for in the real world. I presented a number of sessions focused on Dynamics/SQL performance, and it became very apparent to me that XML SHOWPLAN, a highly useful feature of SQL Server, is not well unde...(truncated)...
Win a signed PowerPivot Architecture Technical Diagram at TechEd NA 2010!
(Indexed 2010-06-06):
[Image]There are many reasons to go to TechEd North America 2010 in New Orleans this year. After all, it is THE event for developers and IT Professionals. And if you want to know more about PowerPivot, here’s yet another reason why you need to go. There will be a raffle to win a signed copy of the PowerPivot Architecture Technical Diagram (screenshot below) in each of the five PowerPivot sessions (five in total). The sessions are:BIE04-INT - Building Custom Extensions to the PowerPivot...(truncated)...
SQL Azure Customer Best Practices
(Indexed 2010-05-28):
The SQLCAT team and the SQL Azure team have been working closely together with a number of customers even before we launched our CTP (Community Technology Preview) in November, 2009 at the PDC (Professional Developers Conference). After our production release in January, 2010, we’ve continued working with some interesting customers and have captured a number of great learnings in these best practice documents. We’ve posted 2 documents to date and have a few more planned over the ne...(truncated)...
Full-text query in local languages
(Indexed 2010-05-20):
Author: Kun ChengReviewer: Alexei KhalyakoLast week, I was presented with an interesting Full-text search problem, which I like to share with you, especially the ones who need to support international markets with SQL Server Full-text solution. The issue was that the customer application not able to find target strings as part of their routine test cycle. Note this customer is planning to enter Asia market with support of local languages. The unit test of Chinese language failed because of the i...(truncated)...
Now that SQL Server 2008 R2 has RTM'ed...
(Indexed 2010-05-14):
We have been pretty excited about the last few weeks where there has been a lot of buzz about SQL Server 2008 R2 RTM. As noted in the SQL Server Data Platform Insider blog posting, this release has a lot of interesting features and is a major release. We have spent a lot of time with many TAP customers and many of the case studies mentioned in the blog are SQLCAT customers including the following case studies: Bank of New Zealand, Studio Moderna CareGroup Healthcare System So what's next...(truncated)...
Resolving DTC Related Waits and Tuning Scalability of DTC
(Indexed 2010-05-11):
Author: Mike Ruthruff Contributors: Gert Drapers, Fabricio Voznika Reviewers: Prem Mehra, Jimmy May, Kun Cheng During a recent performance lab we encountered a large number of waits related to the use of distributed transactions. The specific application being tested used COM+ components and made heavy use of DTC transactions managed by MSDTC. Each component was marked as either requires a transaction or supports a transaction. This resulted in all of the database calls through this layer enl...(truncated)...
Character data type conversion when using SQL Server JDBC drivers
(Indexed 2010-04-05):
In a recent customer engagement, I ran into an interesting situation, which Id like to share, especially with SQL Server users using Microsoft JDBC drivers. It started with typical SQL Server performance tuning exercise to identify the most expensive queries running in SQL Server. The top query looks quite simple:SELECT contact FROM tbl_Customers WHERE ID = @P0 The query took about 3ms to run, which was not bad. However it got executed millions of times and played a major role in limiting the ov...(truncated)...
Moving the Transaction log file of the Mirror Database
(Indexed 2010-04-05):
Author: Sanjay MishraReviewers: Prem Mehra, Mark Souza, Kun Cheng, Nicholas Dritsas, Thomas Kejser Recently I worked with a customer who has deployed database mirroring for a very large database (~ 1 TB) between two data centers that are about 400 miles apart. I got an email from the customer that reads something like the following:While establishing DBM, we restored the log file on the mirror to a different location than its corresponding location on the principal. We had to do it for some har...(truncated)...
Using SQL Agent Job Categories to Automate SQL Agent Job Enabling with Database Mirroring
(Indexed 2010-04-01):
In SQL Server 2005, 2008 and 2008 R2 Database Mirroring works at the individual database level. One challenge to using database mirroring is to keep your important SQL Agent jobs enabled or disabled depending on a database's current mirroring role. If a database is acting as a Principal, you will want the SQL Agent jobs associated with that database to be enabled, while if it is acting as a Mirror, you will want to disable the SQL Agent jobs associated with that database. This does not happen a...(truncated)...
SQL Server 2008 R2 UNICODE Compression what happens in the background?
(Indexed 2010-03-03):
SQL Server 2008 R2 added a much requested feature: Unicode compression. It addresses the need to compress Unicode strings. It is implemented as part of ROW compression, which was added in SQL 2008. That is; if ROW compression (on SQL 2008 R2) is enabled on a table that contains NCHAR / NVARCHAR datatypes, then the Unicode compression algorithm will kick in for each individual Unicode column.Note: since PAGE compression is a superset, which also includes ROW compression, it will also enable Unico...(truncated)...
Enabling Partition Level Locking in SQL Server 2008
(Indexed 2010-03-03):
Author: Thomas Kejser Reviewers: Juergen Thomas, Sanjay Mishra, Stuart Ozer, Lubor Kollar, Kevin Cox, Kun Cheng In this blog, we will provide additional details on a well-hidden feature in SQL Server 2008 partition-level locking. But before we illustrate the feature, heres a bit of background about lock escalation and lock granularities. Background SQL Server has a lock manager that tracks which database objects are locked. Typically, a query will take locks at the row or page level, bu...(truncated)...
Performance tips of using XML data in SQL Server
(Indexed 2010-03-01):
Author: Kun ChengReviewers: Peter Carlin, Mike Ruthruff, Thomas Kejser, Nicholas DritsasXML data type is usually used to store semi-structured data with great flexibility and query capabilities. Its a good choice for developing platform agnostic applications to separate storage of complex data from processing at application layer, which understands the complex data relationship. To achieve optimal performance of querying XML data in SQL Server, extra steps need to be taken to ensure query access...(truncated)...
Full Text Indexing Terabytes of Files with SQL Server and Cloud Storage
(Indexed 2010-02-03):
Author: Darko Sancanin, Nicholas DritsasReviewers and contributors: Lubor Kollar, Stuart Ozer, Michael Thomassy Business CaseThere are currently over 50 million files (over 10 Terabytes of data) that the customer is migrating into a custom project management application (for international customers) that can be accessed via the application and can be searched upon. SQL Server 2008 full text indexing is used to index the content of these files which gives them rich searching capabilities within ...(truncated)...
Whitepaper About Star Join Optimizations
(Indexed 2009-12-16):
When we talk to Data Warehouse customers they often raise questions about the star joins improvements SQL Server 2008. Star join is a set of optimizations that provide some interesting performance gains in data warehouse workload. Instead of digging into details here on SQL CAT we want to draw your attention to this nice whitepaper about the subject: Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries Happy reading.[Image]
Monitoring free space in tempdb transaction log
(Indexed 2009-12-14):
Author: Thomas Kejser Reviewers: Peter Byrne, Sunil Agarwal, Prem Mehra, Peter Scharlock, Lindsey Allen, Mark Souza As part of your database monitoring, you may be keeping track of the free space in the transaction log. One reason to do this, is to detect a rogue query consuming too much transaction log space. If you have such monitoring set up, you need to be a bit careful about tempdb. Recall that unused space in the transaction log in a FULL and BULK LOGGED recovery mode database is rec...(truncated)...
Performance Comparison between Data Type Conversion Techniques in SSIS 2008
(Indexed 2009-12-01):
Authors: Sedat Yogurtcuoglu and Thomas Kejser Technical Reviewers: Kevin Cox, Denny Lee, Carl Rabeler, Dana Kaufman, Jesse Fountain, Alexei Khalyako, Dana Kaufman Overview Data type conversion is one of the most common ETL tasks used in Microsoft SQL Server Integration Services packages. There are several ways to perform these conversions: you can use a Data Conversion transformation or a Derived Column transformation, or you can perform the conversion on the s...(truncated)...
PowerPivot for SharePoint Installation Guides DRAFT
(Indexed 2009-11-20):
The installation of PowerPivot for SharePoint can be a little complex at times - especially the PowerPivot for SharePoint multi-server farm scenario. In order to help with this, the Analysis Services team and the SQLCAT team came together and created four PowerPivot for SharePoint Installation Guides for your review.These are DRAFT guides that you can review and provide feedback on. Meanwhile, happy installing!For more information, please go to PowerPivot Server Installation. [Image]
PowerPivot Jump Start!
(Indexed 2009-11-10):
As you may already know, the new name for Project Gemini is PowerPivot and the official names are PowerPivot for Excel and PowerPivot for SharePoint. For information and to sign up for notification on the release dates, check out the official PowerPivot web site at http://www.powerpivot.com/Shortly after the SharePoint conference, our Analysis Services teammates independently created their own very interesting blogs:Rob Collies PowerPivotPro.com: Rob is one of the founding engineers behind Powe...(truncated)...
Are you going to SQL PASS Nov. 3rd- Nov. 5th?
(Indexed 2009-10-19):
Are you going to SQLPASS in Seattle on Nov 3rd-Nov 5th? If you are going or even thinking about it, then read about how you can spend some quality time with the SQLCAT team. We will be there with our famous ugly lime green shirts, so you wont miss us. The new exciting addition the SQLCAT will partake in this upcoming PASS is the SQL Server Clinic. The SQL Server Clinic will be open every day during PASS starting after the keynote until 6:00pm. In the SQL Server Clinic will be the powerf...(truncated)...
Looking deeper into SQL Server using Minidumps
(Indexed 2009-09-11):
Author: Thomas Kejser Reviewers and Contributors: Bob Ward, Michael Thomassy, Juergen Thomas, Hermann Daeubler, Mark Souza, Lubor Kollar, Henk van der Valk (Unisys) and Peter Scharlock For advanced troubleshooting and understanding of SQL Server, you can sometimes benefit from creating a dump file of the sqlservr.exe process. What is a dump? It is a file containing a snapshot of the running process and parts or all of the memory space of that process. The snapshot also contains the call sta...(truncated)...
Using HierarchyID in SQL Server
(Indexed 2009-08-27):
Implementing a hierarchy structure in a relational data base normally takes a bit of work. The new SQL Server data type for hierarchyID gives a good shortcut to the old methods, makes it faster to get a solution in place and makes it much easier to maintain.MSDN has a good tutorial on HierarchyID showing the old method using a relational table design, followed by another design using the new HierarchyID. This article shows a good tip of how to easily get all descendents of a parent by adding a...(truncated)...
Gemini - Self Service BI!
(Indexed 2009-08-16):
Gemini is the code name for the new breakthrough Self-Service Business Intelligence (BI) capabilities being delivered in the SQL Server 2008 R2 release. Gemini enables end users to build BI applications by integrating data from a variety of sources, modeling, refining and analyzing the data, adding business logic, building reports and visualizations and ultimately sharing it with their coworkers in an environment that is managed and secured by IT. You should bookmark the Gemini Team Blog for the...(truncated)...
Multi user SSAS writebacks may result to blocks on similar functions and new connections.
(Indexed 2009-07-23):
Author: Nicholas Dritsas Reviewers: Akshai Mirchandani, Kevin Cox, Lubor Kollar, Thomas Kejser Writeback consists of two distinct processes. The first one is an update cube process that updates the current session with the changes. Only the current user sees the changes and he can continue with updates and what/if analysis. The second process is a commit so the changes get committed in the database and all users can see the results. The challenge is when you have many users i...(truncated)...
Fast ordered delete
(Indexed 2009-05-21):
We have a visit from MySpace database team in our lab this week. I have used this opportunity and asked Kevin Stephenson, MySpaces Senior Database Developer, about pain points he encounters while doing his everyday job supporting SQL Server. He brought up a problem of efficient ordered delete. He needs to delete large portions of older custom log entries periodically and he needs to delete them in certain order. He also knows that it is efficient to break huge update operations into more small o...(truncated)...
Assigning surrogate keys to early arriving facts using Integration Services
(Indexed 2009-05-13):
In data warehouses, it is quite common that fact records arrive with a source system key that has not yet been loaded in the dimension tables. This phenomena is known as late arriving dimensions or early arriving facts in Kimball terminology.When you see a fact record that cannot be resolved to a dimension surrogate key, the typical solution is this:Create a dummy member in the dimension table using the source system key Assign a surrogate key to this dummy member Use the newly create surrogate ...(truncated)...
Proper partitioning can improve dramatically the writeback process when dealing with large data sets
(Indexed 2009-04-16):
Proper partitioning can improve dramatically the writeback process when dealing with large data setsAuthor: Nicholas DritsasReviewers: Richard Tkachuk, Akshai MirchandaniCASE: Customer is using SQL 2008 and SSAS's writeback abilties to do 52-weeks rolling sales forecasting. Their fact table has reached 250 million rows and they want to add 40 million records into the writeback table each week using weight allocation when updating 2 measures and executing 20,000 update cube statements per week. ...(truncated)...
SQL Resources for Compliance
(Indexed 2009-04-15):
Author: Denny LeeReviewers: Prem Mehra, Kevin Cox With the terabytes of data that are being stored today - truly representing the data explosion that we've always talked about - it's becoming more and more difficult to provide an audit trail or utilize tools that can ensure that your SQL environment is compliant. To help resolve this problem, we've introduced some guidance in the form of our SQL Server Reaching Compliance Guide. We have built up more resources and case studies so you can refer...(truncated)...
Using ProcessingGroup Dimension property option ByTable vs. ByAttribute may error with string keys
(Indexed 2009-04-01):
Author: Nicholas DritsasReviewers: Thomas Kejser, Stuart OzerIn SSAS 2005 and later, there is a dimension property called ProcessingGroup. It has two values; ByAttribute (default) and ByTable. When you use ByAttribute, SSAS will send a SELECT DISTINCT query to the relational engine for each attribute PLUS, at the end, an additional SELECT DISTINCT that combines all the attributes plus key. As you can imagine, if you have several attributes and a very large dimension table, this process can t...(truncated)...
Useful links for upgrading to SQL Server 2008
(Indexed 2009-03-27):
There is plenty of material available to help you upgrade to SQL Server 2008. This blog is intended as a short list for the most useful guidance that I have found. And you may have different experiences upgrading from SQL Server 2000 and SQL Server 2005 so please take the time to do proper preparation work and advanced studying. And test your upgrades before you do it for real in production. There is one bug that you need to know about that potentially affects your upgrade experience. If y...(truncated)...
Remember to move DTS2000 packages when upgrading msdb from 2005 to 2008
(Indexed 2009-03-20):
Some customers prefer to upgrade to SQL Server 2008 by detaching all databases from their SQL Server 2005 instance and then reattaching them to a completely new SQL Server 2008 instance. If you choose this upgrade strategy, you should be aware that you cannot detach an msdb database from a SQL Server 2005 instance and reattach it to a SQL Server 2008 instance. If you try, you will get an error message. Backup/restore will not work for msdb in this upgrade scenario either. The msdb database may...(truncated)...
Analysis Services Partition Size
(Indexed 2009-03-13):
Thomas and I updated the SQL Server 2008 Analysis Services Performance Guide principally to address features available in the latest release. But there was one important change related to partition size driven by changes in hardware. The earlier version of the performance guide for SQL Server 2005 Analysis Services Performance Guide stated this: In general, the number of records per partition should not exceed 20 million. In addition, the size of a partition should not exceed 250 MB. This guida...(truncated)...
SQL Server 2008s Oracle destination fast load option may fail if certain Oracle system views are missing
(Indexed 2009-03-03):
Author: Nicholas DritsasReviewers: Lubor Kollar, Michael Thomassy, Sanjay Mishra SQL Server 2008s latest feature pack, that can be found here, contains a new Oracle connector by Attunity that supports Oracle versions 9.2.0.4 and higher. When you use this connector in SSIS 2008 to send data into Oracle, you may not be able to use fast load if some Oracle system views are missing. This is the case in Oracle 9.2.0.7, but, not 10.2.x. When we tried to use fast load option using Attunitys Oracle de...(truncated)...
Using BIDS 2008 to validate Analysis Services 2005 cubes
(Indexed 2009-02-26):
If you have experimented with cube design in SQL Server 2008, you will probably have run into the new AMO design warnings in Business Intelligence Development Studio 2008 (BIDS). We find that these warnings help customers a lot: they allow them to quickly analyze your cube for classic design mistakes and provide advice on how to correct them. In Analysis Services 2005 you need the best practice analyzer to get the same warnings. Did you know that you can use BIDS 2008 to connect to Analysis Serv...(truncated)...
New Version of Partition Management Utility Available
(Indexed 2009-02-23):
Im happy to announce availability of an updated version of the SQL Server Partition Management tool now offering full support for SQL Server 2008 as well as improvements in handling SQL Server 2005. The tool is available along with source code at http://www.codeplex.com/SQLPartitionMgmt. If youre not already familiar with the tool, it provides a set of commands (at the Command Line or via Powershell) to create a staging table on-demand (including all appropriate indexes and constraints) based...(truncated)...
The Data Loading Performance Guide - Now available from MSDN
(Indexed 2009-02-12):
By popular request, the SQL Customer Advisory Team has collected our lessons learned about tuning data loading into a new whitepaper.We call it the Data Loading Performance Guide and you can find it on MDSN: http://msdn.microsoft.com/en-us/library/dd425070.aspx In this paper, you can find all the tuning tricks we applied to achieve the ETL World Record and a full tuning methodology. We also provide a full overview of the bulk load methods and solutions to common data loading scenarios. Happy dat...(truncated)...
Minimize downtime with DB Mirroring
(Indexed 2009-02-09):
At one high volume OLTP project we are involved in, the customer likes to use DB Mirroring to minimize downtime for planned failovers. This usually involves patching either Windows or SQL Server. The interesting part of this strategy is that they switch to synchronous mirroring just before doing a failover. They would normally like to run with synchronous mirroring but their volume is too high during the day to keep up. So they run asynchronous mirroring until just before they want to switch...(truncated)...
Ultimate guide to upgrading to SQL Server 2008
(Indexed 2008-12-08):
For the ultimate guide to upgrading to SQL Server 2008, please refer to the SQL Server 2008 Upgrade Technical Reference Guide.A successful upgrade to SQL Server 2008 should be smooth and trouble-free. To achieve that smooth transition, you must devote plan sufficiently for the upgrade, and match the complexity of your database application. Otherwise, you risk costly and stressful errors and upgrade problems. Like all IT projects, planning for every contingency and then testing your plan gives yo...(truncated)...
SQL Server 2008 Analysis Services Performance Guide now available
(Indexed 2008-11-14):
Thomas Kejser and I made some significant updates to the SQL Server Analysis Services Performance Guide for SQL Server 2008 - it's available here.Our hope is to keep it alive and make updates as necessary. So if there is something you'd like to see added or have any comments at all, shoot me a note at richtk@microsoft.com.- Richard Tkachuk [Image]
Best Practices for Integrated Full Text Search (iFTS) in SQL 2008
(Indexed 2008-11-06):
This blog is a result of lessons learned while working with the new Integrated Full Text Search in SQL Server 2008. It is not intended to be an in-depth tutorial on how to implement it. Instead, it will concentrate on best practices. The basics of how the new full text search feature works is found in Books On Line.If you would like a good introductory tutorial, start with SQL 2008 Books Online, then read this whitepaper: http://msdn.microsoft.com/en-us/library/cc721269(SQL.100).aspx. It is...(truncated)...
12,000 Concurrent Siebel CRM 8.0 Users On SQL Server 2008
(Indexed 2008-10-29):
Recently, Wanda He from SQLCAT ISV team worked with Oracle Siebel group completed a new 12,000 concurrent users Siebel CRM 8.0 benchmark on SQL Server 2008 (http://www.microsoft.com/isv/oracle/). The benchmark demonstrated that the combination of Microsoft Windows Server 2008, SQL Server 2008, Siebel CRM Release 8.0 architecture and HP BL460c/BL680c Servers is a powerful and cost effective CRM solution. You can read details on the benchmark test configuration and results in the whitepaper: htt...(truncated)...
Reporting Services Scale-Out Deployment Best Practices
(Indexed 2008-10-22):
Check out the the third of five technical note as part of the Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series: Reporting Services Scale-Out Deployment Best PracticesThis technical note reviews the SSRS Scale-Out ArchitectureReport Catalog sizingThe benefits of File System snapshots for SSRS 2005Why File System snapshots may not help for SSRS 2008Using Cache ExecutionLoad Balancing your NetworkIsolate your workloadsReport Data Performance Consid...(truncated)...
Reintroducing sqlcat.com
(Indexed 2008-10-16):
Check out the new look and feel of the sqlcat.com! Now you have easy access and views of our Top 10 Lists, Technical Notes, Whitepapers, and Toolbox. As well, now our blogs our mirrored to sqlcat.com for easy access to all SQLCAT tips, best practices, and lessons learned.Right now, you can see our graphic noting the "SQLCAT Track at PASS" where the SQLCAT team will have 15 sessions providing indepth technical learnings and best practices from some of the largest real-world SQL implementations....(truncated)...
Table variable vs. temp table and estimated vs. actual query plan
(Indexed 2008-10-09):
The other day we got a call from a puzzled friend. He has rewritten a stored procedure using table variable instead of temp table, it makes the stored procedure code look more tidy. The puzzling part is the same stored procedure now running a lot slower. The estimated plans looked the same using temp table and table variable, but the execution plans are very different. Unbeknownst to him, rewriting the procedure using table variable bumped into a query optimizer blind spot. While the “Esti...(truncated)...
Latest Publications: Top 10 SQL Server Integration Services Best Practices and "Money" Technical Notes Published
(Indexed 2008-10-09):
After the great work from the ETL World Record (for more information, refer to our other blog at: http://blogs.msdn.com/sqlcat/archive/2008/09/18/scaling-heav...(truncated)... Thomas and I (with the help of many others) have created a "Top 10 list" of the best practices for SSIS that can be found on sqlcat.com: Top 10 SQL Server Integration Services Best Practices As well, everyone knows the benefits of "money"...but not everyone may know the benefits of the money data type. So if you're up for...(truncated)...
Project Gemini - building models and analysing data from Excel
(Indexed 2008-10-06):
Saw an amazing BI demo this morning at the BI Conference here in Seattle. Donald Farmer showed how over 20M rows of data can be modeled and analyzed in memory. To build a model today, a DBA needs to define dimensions and fact tables, get the relationships right, define calculations, deploy it to a server, build and manage it. After that, someone can connect to it and play with the data. What Donald showed is how a user can do all that with an Excel add-in. He started with 20 million rows in memo...(truncated)...
SQL PASS 2008 Just may be the best PASS Conference yet
(Indexed 2008-09-25):
Coming up this November 18-21st is the SQL PASS Conference in Seattle, WA (right in Microsofts backyard). See http://summit2008.sqlpass.org/ for all the details. This SQL PASS will be very special. The SQL Server engineering team is sending up to 300 developers to the conference. You wont have to walk very far in order to run into one. To meet the engineers 1:1 and discuss your SQL Server applications should be a big justification for attending. The SQLCAT SQL Server Customer Advisory Tea...(truncated)...
Scaling Heavy Network Traffic with Windows
(Indexed 2008-09-18):
Under Windows Server 2000 and 2003 (RTM), the interrupts from a single network adaptor (NIC) cannot be handled by multiple CPU Cores. A Deferred Procedure Call (DPC) call gets scheduled to run as a consequence of the NIC firing an interrupt. The DPC will deliver the received packets from the NIC to the networking subsystem of the OS. The NIC driver will block interrupts from the network card until the DPC has been handled. If your system makes heavy use of network bandwidth, sending all interrup...(truncated)...
IIF Function Query Hints in SQL Server Analysis Services 2008
(Indexed 2008-09-03):
Thomas Kejser and I are doing some work to produce the SQL Server 2008 Analysis Services Performance Guide. Among a bunch of other things, it discusses the IIF function. But because I recently mentioned MDX query hints in a recent podcast, I wanted to get this out quickly. Some references are a bit hazy (expensive vs inexpensive query plans, default values and the like) and Ill elaborate either in future blogs or in the white paper itself.Anyway, the IIF mdx function is a commonly used expressio...(truncated)...
Microsoft SQL Server Database Snapshots and Synonyms
(Indexed 2008-08-05):
OVERVIEW: One of the common complaints in using database snapshots is how to get queries and reports to switch over and start using the new snapshot as soon as it is available. There are three basic ways to accomplish this:1. Delete the old and rename the new database snapshot. This works well if no users are using either snapshot.2. Changing the connection string. This works pretty well and has the advantage that long running reports can continue to run while new reports/queries...(truncated)...
New Best Practices Articles Published - Running Microsoft SQL Server 2008 Analysis Services on Windows Server 2008 vs. Windows Server 2003 and Memory Preallocation: Lessons Learned
(Indexed 2008-07-16):
Due to the improvements in the Windows Server 2008 memory manager related to the change in the algorithm for scanning pages in memory, SQL Server 2008 Analysis Services performed equally well during both partition and dimension processing with or without memory preallocation when running on Windows Server 2008. However, SQL Server 2008 Analysis Services performed substantially better during both partition and dimension processing with the use of the memory preallocation configuration setting whe...(truncated)...
New Best Practices Articles Published - Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned
(Indexed 2008-07-16):
Reporting Services 2008 was able to respond to 34 times the total number of users and their requests on the same hardware without HTTP 503 Service Is Unavailable errors compared with Reporting Services 2005, regardless of the type of renderer. In stark contrast, Reporting Services 2005 generated excessive HTTP 503 Service Is Unavailable errors as the number of users and their requests increased, regardless of the report renderer. Our tests clearly show that the new memory management architecture...(truncated)...
SQL Server Scale Out
(Indexed 2008-06-12):
After a few recent conversations with CIO and CTO types, I was amazed that there is still the concept out there that SQL Server doesnt scale well. So I sat down to write a lengthy blog to dispel that rumor. After doing some research, I found that there is so much already written and available on the web that I decided to gather all the links in one spot and make this blog a launching point for your own research and reading pleasure.After reading these, Im sure you will agree that the SQL Serve...(truncated)...
What you should know if you are using Logical IOs to measure query plan quality
(Indexed 2008-04-10):
One of the presenters at recent SQL Server Open World conference in Denmark (http://www.miracleas.dk/index.asp?page=168&page2=323) suggested tuning SQL queries using logical IOs to measure quality of query plan. The IO count is returned for each object accessed in your query after you issue set statistics IO on. Here is an example of returned message:Table 't1'. Scan count 10, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads...(truncated)...
Interesting observation with table > 1TB
(Indexed 2008-04-08):
Interesting observation with table > 1TBWe have a project using SQL 2005 with a table that is several terabytes. This table has a varbinary(max) column with an average length of 150k bytes. We needed a second copy of the table on a smaller test server to do some other tests and decided that SELECT INTO was our best option because it is minimally logged. The smaller test server is 4CPU with 8GB RAM. Since we had all the SQL products installed on the test server, there was only 4GB allocated to...(truncated)...
Backup More Than 1GB per Second Using SQL2008 Backup Compression
(Indexed 2008-03-02):
Backup Compression in SQL2008 performs very well and it is easy to use. You could either use “With Compression” along with the “Backup” command or simply enable default backup compression through sp_configure by setting ‘backup compression default’ value to 1. It is very convenient for backups of large databases in our customer lab environment. A few things you could do to make the backup compression perform better. The hardware we used is 32way HP Superdome...(truncated)...
SSIS 2008 - world record ETL performance
(Indexed 2008-02-29):
Using SQL Server 2008 and SSIS 2008 the SQL Server Performance team working with Unisys and SQL CAT have shown SSIS scaling to world record performance! We loaded 1.18 TB in under 30 minutes. You can find more information here: http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx Also watch for a paper from the performance team about how this was achieved and lessons learned in this exercise. In addition, the SQL CAT team have been doing work on fast data loads using bulk in...(truncated)...
Now is time to try SQL 2008
(Indexed 2008-02-24):
SQL Server 2008 is just around the corner. SQLCAT has been working with our customers and partners implementing pre-release builds. We have a lot good lessons learned to share, here are some of the upcoming best practices papers: · Index Defragmentation Best Practices · Best Practices for using Resource Governor with a mixed (OLTP and Reporting) workload · Data Compression: Design and Implementation Best Practices, Performance Impact · XEvents Toolkit · SQL 2008 Usa...(truncated)...
Introducing SQLCAT.com
(Indexed 2008-02-02):
The SQL Customer Advisory Team has put the final touches on the site http://sqlcat.com/ which is our new hub for all things that are part of the SQLCAT! Our new site is a portal to all SQLCAT resources including:Top 10 Lists: Summary list of Best Practices and RecommendationsTechnical Notes: Deep level technical short papers Technical Spotlights: Technical end-to-end customer case studiesSearchable and Tagger friendly; post comments and provide feedback!Search all of our SQLCAT Best Practices W...(truncated)...
New Partition Management Tool Available on Codeplex
(Indexed 2008-01-28):
As anyone working with partitioned tables knows by now, the process of creating stand-alone staging tables to support switching data into or out of a partition can be a management challenge, especially if the design of the partition table or its indices change over time. A staging table for new data to be switched-in must include check constraints that match the partition boundaries of the target, and include all columns, constraints and indexes appearing in the partition table. Often, the ...(truncated)...
FoxPro to SQL Server migration experience
(Indexed 2008-01-25):
At first I was going to write a detailed white paper about the conversion effort. But someone has already done it. There is a series of very good guides at:http://www.ciol.com/content/special/foxpro/foxpro_Special.htmSince FoxPro is nearing its end of life, the company needed to convert the system to newer technology. They chose Microsoft SQL Server and Microsoft .NET. There was over 1 terabyte of data being processed in FoxPro.The project was fairly easy as far as the data conversion goes. ...(truncated)...
New Best Practices Whitepaper published: "Best Practices for Migrating Non-Unicode Data Types to Unicode"
(Indexed 2008-01-21):
The database system in a company that is growing internationally must support multilingual characters in tables with Unicode data types. Existing databases that support only non-Unicode information must be migrated from non-Unicode data type to Unicode data type. This paper summarizes best practices for migrating database systems from non-Unicode to Unicode data type.http://download.microsoft.com/download/d/9/4/d948f981-9...(truncated)...
SQL Server 2005 Best Practices Analyzer (January 2008) -- Now Available!
(Indexed 2008-01-18):
It's a new year and, with it, we come bearing gifts! We have a pretty significant update to SQL Server 2005 Best Practices Analyzer. It contains many new and updated rules for Analysis Services, a few important rules for the Relational Engine, a couple bug fixes for the UI and command line tools. And all of these rules have rich documentation telling you what need to know.......(read more)[Image]
Querying Dimension Members In Analysis Services
(Indexed 2008-01-16):
Many Analysis Services applications query members to populate a dropdown or find those that match some criteria starting or ending with a string, existing with other members, having a member property with a certain value, whatever. For example, say I have a bird hierarchy and I want to find all birds with the expression eagle somewhere in the member name. I could do something like this:Select filter(birds.members, instr(1, Birds.currentmember.name, "eagle") > 0) on columns from [birds]But this ...(truncated)...
DB Autoshrink option may introduce high fragmentation
(Indexed 2008-01-11):
IssueWe have seeing some strange behavior when rebuilding indexes. Total Fragmentation prior to rebuild is 99%. You rebuild the index. (using both the alter index and the dbcc dbreindex) Fragmentation is at 0% You recheck fragmentation about a minute later. Fragmentation is at 99% again. You have verified that there are no DML's being done on that table. AnalysisWe noticed that the database has Auto Shrink enabled. StatusAfter we disabled th...(truncated)...
New Best Practices Articles Published - Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques
(Indexed 2007-12-21):
Many-to-many dimension relationships in SQL Server 2005 Analysis Services (SSAS) enable you to easily model complex source schemas and provide great analytical capabilities. This capability frequently comes with a substantial cost in query performance due to the runtime join required by Analysis Services to resolve many-to-many queries. This best practices white paper discusses three many-to-many query performance optimization techniques, including how to implement them, and the performance test...(truncated)...
New Best Practices Whitepaper Published: The Impact of Changing Collations and of Changing Data Types from Non-Unicode to Unicode
(Indexed 2007-12-06):
When a business grows internationally, its database system must support multilingual characters in tables. Businesses that previously needed to manage only non-Unicode information must now support database tables that include non-Unicode data types such as CHAR or VARCHAR. As the business grows internationally, it must alter its database system to support Unicode data types. It may also need databases that have different collations across several severs. http://download.microsoft.com/download/d/...(truncated)...
Using ByAttribute or ByTable Processing Group Property with Analysis Services 2005
(Indexed 2007-10-20):
As noted within the Analysis Services 2005 Performance Guide, there are some niche situations where setting the Processing Group property to ByTable provides more optimal processing than the default value of ByAttribute. In a customer scenario, we had discovered that they had two dimensions (each of which has >25 million members and 8-10 attributes) where the Processing Group property was set to ByTable. While the ByTable setting could theoretically have Analysis Services process faster (becaus...(truncated)...
SSAS small/big query interaction
(Indexed 2007-10-20):
Default SSAS behavior can sometimes result in small queries being slowed down by concurrently running big queries.Recently in working with a customer, we observed the most common queries when run by themselves took about 1 second and some ad-hoc queries against their 110 GB cube took about 3 minutes. When running both together, small query times slowed down to several minutes. This slowdown prevented the customer's project from being successfully deployed.There is a configuration parameter tha...(truncated)...
Precision Considerations for Analysis Services Users Whitepaper now available
(Indexed 2007-10-09):
For those of you whom want to know a little more about precision considerations for Analysis Services users, please check out this recently released whitepaper at http://www.microsoft.com/downloads/details.aspx?familyid=bae...(truncated)... white paper covers accuracy and precision considerations in SQL Server 2005 Analysis Services. For example, it is possible to query Analysis Services with similar queries and obtain two different answers. While this appears to be a bug, it actually is due to ...(truncated)...
Using unformatted cell values it may improve query performance
(Indexed 2007-09-25):
If a report does not use formatted cell values, query time can be reduced. An example is a Reporting Services report that does its own formatting and therefore would not use the formatted values from SSAS. By returning only the value of the cell and not its formatted value in MDX, you can achieve better queries performance, sometimes between 5-20%, depending on the size of the cell set. The default properties include FORMAT_STRING and FORMATTED_VALUE, and can be omitted by specifying a list ...(truncated)...
New Best Practices Article
(Indexed 2007-09-12):
Scale-Out Querying with Analysis Services Using SAN Snapshots [Image]
SQLCAT tracks at 2007 SQL PASS
(Indexed 2007-09-12):
As you may already know from Brian Moran's SQL Server Magazine article "SQLCAT to Offer Six Sessions at PASS", the SQLCAT team will be providing 6 SQLCAT tracks at the 2007 SQLPASS event in Denver this year. As of today's schedule, below are the SQLCAT tracks schedule: Wednesday, September 19th 9:45am-11:00amDBA-410-MDesigning for Petabyte using Lession Learned from Customer ExperiencesLubor Kollar, Microsoft; Kevin Cox, MicrosoftKorbel 1C/1D Wednesday, September 19th 1:30pm-2:45pmDBA-411-MBui...(truncated)...
SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
(Indexed 2007-08-28):
Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis ServicesThere are a variety of things that you can do to improve the performance of an individual MDX query. To begin, you must identify the source(s) of the performance bottlenecks in the execution of a poorly performing MDX query. To identify the source(s) of performance bottlenecks, you need to understand how to use the available Microsoft Windows and SQL Server 2005 Analysis Services troubleshooting tools...(truncated)...
Renaming Olap Databases Issues within Analysis Services
(Indexed 2007-07-30):
If you are view the XMLA script that is generated for an Analysis Services database, you will notice that there are both ID and NAME attributes. The reason Analysis Services has this differentiation is so one could rename an Olap database (which you could not do officially within Analysis Services 2000). Database name defaults to its ID and the ID cannot be modified. You can, on the other hand, change the database name. This way end-users could have a different name to the Olap database irrelev...(truncated)...
Performance Analysis Tool - DMVStats
(Indexed 2007-07-13):
Tom Davidson wrote: I've been working with database applications for the last 25 years. A particular area of interest for me is SQL Server performance. As part of the Microsoft SQL Server Development Customer Advisory Team (SQLCAT) for the last 7 years, I've had an opportunity to observe what works (and more importantly what doesn't !) in the enterprise space. One of the key enterprise needs prior to SQL Server 2005, was an effective mechanism for troubleshooting and performance tuning for many ...(truncated)...
DMV Stats Toolkit published on CodePlex
(Indexed 2007-07-06):
http://www.codeplex.com/sqldmvstats Also, linked from SQL Server Best Practices website: http://technet.microsoft.com/sqlserver/bb331794.aspx[Image]
Distributed Partitioned Views / Federated Databases: Lessons Learned
(Indexed 2007-06-20):
IntroductionThis article contains information about the things we have learned while working with Federated Databases. Before beginning it is necessary to define the terms being used. Included in this article is one solution in production that is using federated servers and Distributed Partitioned Views. DefinitionsDefinition 1: Local Partitioned View A single table is horizontally split into multiple tables, usually all have the same structure. Example 1:Original table is named FACT.New tab...(truncated)...
Inconsistencies when using Drillthrough MDX with Perspectives
(Indexed 2007-06-18):
Author: Denny LeeContributors: John Desch, Kevin CoxReviewers: Robert Zare DRILLTHROUGH MDX OverviewDRILLTHROUGH MDX is a very powerful way to drillthrough to your original data source of detail data that make up the aggregations that you are looking at. For more information on enabling drillthrough, please refer to T.K. Anands great article on Enabling Drillthrough in Analysis Services 2005 (http://technet.microsoft.com/en-us/library/ms345125(SQL.90).aspx). Using a real example, if you were t...(truncated)...
Three new best practices articles are available
(Indexed 2007-06-12):
Three new best practices articles are available at the SQL Server Best Practices site on Technet (http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx).Two of the articles are on Analysis Services Processing and Querying best practices:Analysis Services Processing Best Practiceshttp://www.microsoft.com/technet/prodtechnol/sql/be...(truncated)... Denny Lee, Nicholas Dritsas, Carl RabelerAnalysis Services Query Performance Top 10 Best Practiceshttp://www.microsoft.com/technet/prodtechnol/sql...(truncated)...
Four new best practices articles are available
(Indexed 2007-06-12):
Four new best practices articles are available at the SQL Server Best Practices site on Technet (http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx).Two of the articles are on Analysis Services Processing and Querying best practices:Analysis Services Processing Best Practiceshttp://www.microsoft.com/technet/prodtechnol/sql/be...(truncated)... Denny Lee, Nicholas Dritsas, Carl RabelerAnalysis Services Query Performance Top 10 Best Practiceshttp://www.microsoft.com/technet/prodtechnol/sql/...(truncated)...
New Best Practices Articles are available (May 2007)
(Indexed 2007-06-08):
You can check out the three new Best Practices Articles Predeployment I/O Best Practiceshttp://www.microsoft.com/technet/prodtechnol/sql/be...(truncated)... Mike RuthruffPartial Database Availabilityhttp://www.microsoft.com/technet/prodtechnol/sql...(truncated)... Danny TambsComparing Tables Organized with Clustered Indexes versus Heapshttp://www.microsoft.com/technet/prodtechnol/sql/bestpr...(truncated)... Burzin Patel, Sanjay Mishra [Image]
Querying a Database Snapshot
(Indexed 2007-06-06):
Authors: Sanjay Mishra, Michael Thomassy, Peter Byrne Database Snapshots (http://msdn2.microsoft.com/en-us/library/ms175158.aspx) in SQL Server 2005 Enterprise Edition allow a read-only point-in-time view of data. Recently we encountered an interesting scenario with a customer implementation. The customer creates a database snapshot. Then a query is executed on the database snapshot. The first time a set of queries are executed, they take 17 minutes. Every subsequent execution of the same querie...(truncated)...
SQL Server 2005 Transactional Replication: Benefit of using SubscriptionStreams for low bandwidth, high latency environments
(Indexed 2007-05-07):
Author: Bren NewmanIntroductionWhen using Microsoft SQL Server 2005 Transactional Replication, the Log Reader Agent reads the log, constructs SQL statements for published articles and posts it to the Distribution database; the Distribution Agent reads the Distribution database, delivers and applies the "package" to the Subscriber. Often the Publisher, Distributor and Subscribers are on fast networks resulting in very low total delivery latencies. However on slower networks such as in a geographi...(truncated)...
New Best Practices Articles are now available
(Indexed 2007-05-03):
You can check out the three new Best Practices Articles SQL Server 2005 Deployment Guidance for Web Hosting Environments http://www.microsoft.com/technet/prodtechnol/sql/bestpractic...(truncated)... Alex DeNeui Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarioshttp://www.microsoft.com/technet/prodtechnol/sql/be...(truncated)... Carl RabelerImplementing Application Failover with Database Mirroringhttp://www.microsoft.com/technet/prodtechnol/sql/be...(...(truncated)...
How to warm up the Analysis Services data cache using Create Cache statement?
(Indexed 2007-04-26):
GoalThis document describes how to build Create Cache commands. Create Cache for Analysis Services (AS) was introduced in SP2 of SQL Server 2005. It can be used to make one or more queries run faster by populating the OLAP storage engine cache first. Some customers have found certain queries benefit other later queries. For example, ascmd.exe could be used every hour to execute all queries in a directory keeping the cache ready for subsequent user queries. The other approach, which has been u...(truncated)...
SSAS Partition Slicing
(Indexed 2007-03-05):
Summary: SSAS uses partitions to contain cube data. Small cubes might use only one, but for non trivial cubes, cube designers will create partitions based on ease of managing data and to split groups of data. This document discusses how the server uses "slices" to examine partitions at query time. The slices are stored as a range of internal surrogate keys, and can be thought of as a very high level index.Using more attributes in the partition definition can potentially improve performance by...(truncated)...
Previously committed rows might be missed if NOLOCK hint is used
(Indexed 2007-02-01):
I received a question from one of our customers about using the NOLOCK hint: can it cause missing rows in scans even if the rows were committed well before my SELECT with NOLOCK starts? The NOLOCK hint is employed by many users to avoid contention on tables where updates are performed concurrently with selects. The popular belief is that the only side effect of using the NOLOCK hint is that it may cause reading uncommitted rows. These would be the rows that may be either rolled back or are par...(truncated)...
SQL Server Best Practices Website
(Indexed 2006-12-08):
http://www.microsoft.com/technet/prodtechnol/sql/bestpractic...(truncated)...
Check out the exciting new SQL Server Best Practices website.....
(Indexed 2006-12-08):
http://www.microsoft.com/technet/prodtechnol/sql/bestpractic...(truncated)... the real-world guidelines, expert tips, and rock-solid guidance to take your SQL Server implementation to the next level. Drawing on the extensive experience and expertise from respected developers and engineers at Microsoft as they walk you through the specifics on solving particularly difficult issues. [Image]
Best SQL Server 2005 MDX Tips and Tricks - Part 1
(Indexed 2006-10-12):
OverviewSQL Servers 2005 Analysis Services has introduced several changes to the MDX queries syntax that can lead to better performance than the equivalent AS 2000 queries. Here is a list of tips and tricks as well as best practices on how to get better performance from your MDX queries in AS2005.DetailsFor filtering a set, use Filter inside Crossjoin vs the other way aroundFilter a set and then use it in the Crossjoin. Filter function materializes the set and iterates through the set to check ...(truncated)...
OLTP Blueprint - A Performance Profile of OLTP applications
(Indexed 2006-06-23):
Performance and Tuning Blue PrintsWe will look at different types of applications, how they use resources, and how one would approach performance tuning each. The performance profile of OLTP differs significantly from a Relational Data Warehouse or Reporting application. It is helpful to understand these differences and the objectives for high performance.OLTP blueprintFor example, OLTP applications are characterized by high volumes of small identical transactions. These can include SELECT, I...(truncated)...
Load 1TB in less than 1 hour
(Indexed 2006-05-19):
OVERVIEW This project was done using SQL Server 2005 Enterprise Edition (with SP1 beta) using the BULK INSERT command with 60 parallel input files, each of approximately 16.67 GB. The best time I got was 50 minutes. Hewlett Packard was kind enough to loan me some time one night on the machine they use to do the TPC-C tests. It is an Itanium2 Superdome with 64 1.6GHZ 9M (Madison) CPUs. For the insert tests the system was equipped with 256GB of RAM. The HP SAN configuration is rated at 14GB p...(truncated)...
SQL Server Sequence Number
(Indexed 2006-04-10):
Simulating Sequence Objects in SQL Server Many applications need sequentially incremental number as unique/primary key of records. SQL Server 2005 today supports identity column as the primary mean to general sequence number, which generates the sequence number upon the execution of DML (insert) or bulk insert. The value of the insertion is known (when using insertion DML), post the execution of the DML. In some implementations, the sequence numbers (or next unique incremental number) need to...(truncated)...
Managing Schema Changes (Part 2)
(Indexed 2006-03-31):
SQLCAT Blog: Managing Schema Changes (Part 2) Question: What are best practices for managing schema changes in SQL Server 2005? What are the fastest, least intrusive techniques for high availability? Answer: In Part 1, we outlined the behavior of SQL Server 2005 schema changes. In Part 2, we will look at best practices techniques to de-couple the batch updates required (see Managing Schema Changes Part 1) for data type and most length changes. These techniques allow us to make schema chan...(truncated)...
2. Application Patterns and database performance: Academically correct object modeling.
(Indexed 2006-03-06):
Another pattern that can cause too many round trips to the database is when each object is treated as a black box and is responsible for retrieving and saving its own data. This by itself is not the problem; its when the object hierarchy reaches three or more levels. The natural programming pattern becomes this: for each row in the parent object, retrieve all the related child rows in a separate query. Two levels in the hierarchy is not too bad unless the final record set is huge. But carr...(truncated)...
Managing Schema Changes (Part 1)
(Indexed 2006-03-01):
Question: What are best practices for managing schema changes in SQL Server 2005? What are the fastest, least intrusive techniques for high availability? Answer: A common requirement as business requirements evolve over time is managing schema changes. While SQL Server 2005 supports the changing of schema (e.g. adding columns and constraints, changing column data types and properties, and dropping columns and constraints), in Managing Schema Changes (Part 1) we will focus on performance and co...(truncated)...
Partition Elimination in SQL Server 2005
(Indexed 2006-02-17):
Lubor Kollar Partition elimination is very important when SQL Server executes queries against partitioned tables or partitioned views. In general, SQL Server is doing an excellent job of not scanning the partitions that are excluded by some predicates. Recently we have discovered one scenario where the partition elimination does not work against partitioned tables in SQL Server 2005 and this blog describes the conditions leading to the problem as well as easy workarounds. Additionally you will l...(truncated)...