Site: http://blogs.msdn.com/b/sqlcat/ Link: http://blogs.msdn.com/sqlcat/rss.xml
by tkejser via Microsoft SQL Server Development Customer Advisory Team on 8/31/2010 10:27:40 PM
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 ...
[ read more ]
by Lubor via Microsoft SQL Server Development Customer Advisory Team on 7/31/2010 12:11:27 AM
Introduction Now 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 In ...
by Peter Scharlock via Microsoft SQL Server Development Customer Advisory Team on 6/9/2010 6:50:51 PM
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 ...
by Michael Thomassy via Microsoft SQL Server Development Customer Advisory Team on 5/28/2010 4:46:00 AM
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 6 best practice documents to the SQL Azure d ...
by Kun Cheng via Microsoft SQL Server Development Customer Advisory Team on 5/20/2010 11:46:00 PM
Author: Kun ChengReviewer: Alexei Khalyako Last 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 ...
by MikeRuthruff via Microsoft SQL Server Development Customer Advisory Team on 5/11/2010 6:31:00 PM
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 ...
by Kun Cheng via Microsoft SQL Server Development Customer Advisory Team on 4/5/2010 9:49:00 PM
Author: Kun ChengReviewers: Wanda He, Kevin Cox, Michael Thomassy, Peter Scharlock In a recent customer engagement, I ran into an interesting situation, which I’d 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 n ...
by Sanjay Mishra via Microsoft SQL Server Development Customer Advisory Team on 4/5/2010 6:39:00 PM
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 i ...
by kevincox via Microsoft SQL Server Development Customer Advisory Team on 4/1/2010 2:32:00 PM
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 hap ...
by tkejser via Microsoft SQL Server Development Customer Advisory Team on 3/3/2010 11:54:00 PM
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, here’s 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, ...
by Peter Scharlock via Microsoft SQL Server Development Customer Advisory Team on 3/3/2010 12:06:00 AM
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 Un ...
by Kun Cheng via Microsoft SQL Server Development Customer Advisory Team on 3/1/2010 11:52:00 PM
Author: Kun ChengReviewers: Peter Carlin, Mike Ruthruff, Thomas Kejser, Nicholas Dritsas XML data type is usually used to store semi-structured data with great flexibility and query capabilities. It’s 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 acc ...
by Nicholas Dritsas via Microsoft SQL Server Development Customer Advisory Team on 2/3/2010 2:52:00 AM
Author: Darko Sancanin, Nicholas Dritsas Reviewers and contributors: Lubor Kollar, Stuart Ozer, Michael Thomassy Business Case There 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 capabi ...
by tkejser via Microsoft SQL Server Development Customer Advisory Team on 12/14/2009 1:58:36 PM
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 ...
by tkejser via Microsoft SQL Server Development Customer Advisory Team on 12/1/2009 1:59:00 PM
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 ...
by marksou via Microsoft SQL Server Development Customer Advisory Team on 10/19/2009 1:47:00 PM
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 won’t 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.&nbs ...
by tkejser via Microsoft SQL Server Development Customer Advisory Team on 9/11/2009 6:43:24 PM
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 st ...
by kevincox via Microsoft SQL Server Development Customer Advisory Team on 8/27/2009 5:48:00 PM
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 Nicholas Dritsas via Microsoft SQL Server Development Customer Advisory Team on 7/23/2009 6:10:00 AM
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 ...
by LuborK via Microsoft SQL Server Development Customer Advisory Team on 5/21/2009 3:54:00 AM
We have a visit from MySpace database team in our lab this week. I have used this opportunity and asked Kevin Stephenson, MySpace’s 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 smal ...
by tkejser via Microsoft SQL Server Development Customer Advisory Team on 5/13/2009 5:26:00 PM
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 s ...
by denny.lee via Microsoft SQL Server Development Customer Advisory Team on 4/15/2009 9:06:00 PM
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 stu ...
by Nicholas Dritsas via Microsoft SQL Server Development Customer Advisory Team on 4/1/2009 3:48:00 AM
Author: Nicholas Dritsas Reviewers: Thomas Kejser, Stuart Ozer In 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 t ...
by kevincox via Microsoft SQL Server Development Customer Advisory Team on 3/27/2009 2:37:00 PM
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 ...
by tkejser via Microsoft SQL Server Development Customer Advisory Team on 3/20/2009 2:06:00 PM
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 msd ...
by richtk via Microsoft SQL Server Development Customer Advisory Team on 3/14/2009 1:41:00 AM
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 ...
by Nicholas Dritsas via Microsoft SQL Server Development Customer Advisory Team on 3/3/2009 3:26:00 PM
Author: Nicholas Dritsas Reviewers: Lubor Kollar, Michael Thomassy, Sanjay Mishra SQL Server 2008’s 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 ...
by tkejser via Microsoft SQL Server Development Customer Advisory Team on 2/26/2009 1:25:00 PM
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 A ...
by stuarto via Microsoft SQL Server Development Customer Advisory Team on 2/24/2009 12:19:00 AM
I’m 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 you’re 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 ...
by tkejser via Microsoft SQL Server Development Customer Advisory Team on 2/12/2009 1:57:00 PM
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. & ...
by kevincox via Microsoft SQL Server Development Customer Advisory Team on 2/9/2009 8:55:00 PM
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 befor ...
by denny.lee via Microsoft SQL Server Development Customer Advisory Team on 12/8/2008 12:57:00 AM
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 y ...
by kevincox via Microsoft SQL Server Development Customer Advisory Team on 11/6/2008 1:43:00 AM
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( ...
by lingzhuz via Microsoft SQL Server Development Customer Advisory Team on 10/29/2008 11:53:49 AM
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 ...
by denny.lee via Microsoft SQL Server Development Customer Advisory Team on 10/22/2008 10:59:00 PM
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 Practices This technical note reviews the SSRS Scale-Out Architecture Report Catalog sizing The benefits of File System snapshots for SSRS 2005 Why File System snapshots may not help for SSRS 2008 Using Cache Execution Load Balancing your Network Isolate your workloads Report Data Performa ...
by denny.lee via Microsoft SQL Server Development Customer Advisory Team on 10/16/2008 5:24:00 PM
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 ...
by lingzhuz via Microsoft SQL Server Development Customer Advisory Team on 10/9/2008 6:54:05 PM
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 ...
by denny.lee via Microsoft SQL Server Development Customer Advisory Team on 10/9/2008 5:05:00 PM
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-heavy-network-traffic-with-windows.aspx), 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. ...
by richtk via Microsoft SQL Server Development Customer Advisory Team on 10/6/2008 6:06:00 PM
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 ...
by tkejser via Microsoft SQL Server Development Customer Advisory Team on 9/18/2008 7:08:18 PM
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 ...
by richtk via Microsoft SQL Server Development Customer Advisory Team on 9/3/2008 11:01:00 PM
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 I’ll elaborate either in future blogs or in the white paper itself. Anyway, the IIF mdx function is a commonly used ex ...
by kevincox via Microsoft SQL Server Development Customer Advisory Team on 8/5/2008 2:48:00 PM
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 ...
by carl.rabeler via Microsoft SQL Server Development Customer Advisory Team on 7/16/2008 6:49:00 PM
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 ...
by carl.rabeler via Microsoft SQL Server Development Customer Advisory Team on 7/16/2008 6:45:00 PM
Reporting Services 2008 was able to respond to 3–4 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 architectu ...
by kevincox via Microsoft SQL Server Development Customer Advisory Team on 6/12/2008 11:00:00 AM
After a few recent conversations with CIO and CTO types, I was amazed that there is still the concept out there that SQL Server doesn’t 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, I’m sure you will agree that ...
The content of the postings is owned by the respective author. SQL Feeds is not responsible for the contents of the postings. This site is automatically generated and cannot be reviewed for abusive content. If you find abusive content on SQL Feeds, please contact us. Designated trademarks and brands are the property of their respective owners. All rights reserved.
