Site: http://blogs.msdn.com/b/sqlcat/ Link: http://blogs.msdn.com/sqlcat/rss.xml
by Denny Lee [MSFT] via Microsoft SQL Server Development Customer Advisory Team on 12/14/2011 6:27:45 PM
We are proud to announce that the community technology preview (CTP) of ApacheTM HadoopTM-based Services for Windows Azure (or Hadoop on Azure) is now available. As noted in on the SQL Server Data Platform Insider blog, the CTP is by invite only http://blogs.technet.com/b/dataplatforminsider/archive/2011/12/14/availability-of-community-technology-preview-ctp-of-hadoop-based-service-on-windows-azure.aspx While Hadoop is important to our customers for performance, scalability, and extreme volumes ...
[ read more ]
by Kun Cheng via Microsoft SQL Server Development Customer Advisory Team on 11/28/2011 10:15:00 PM
Author: Kun Cheng Reviewers: Shaun Tinline-Jones, Silvano Coriani, Steve Howard, Thomas Kejser, Sanjay Mishra A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators, but the expression cannot be a subquery. A simple example of a computed column is: Col1 Col2 Computed_Col=(Col1+Col2) 100 100 200 A benefit o ...
by Thomas Kejser via Microsoft SQL Server Development Customer Advisory Team on 11/28/2011 2:21:44 PM
Author: Thomas Kejser Contributors/Reviewers: Alexei Khalyako, Jerome Halmans, Fabricio Voznika, Sedat Yogurtcuoglu, Mike Ruthruff, Tobias Ternstrom and Steve Howard In this blog, I will explore ideas for extending SQL Server with new, fast hash functions. As will be shown, the high speed, built in functions CHECKSUM and BINARY_CHECKSUM are not always optimal hash functions, when you require the function to spread data evenly over an integer space. I will show that it can be useful to extend S ...
by Denny Lee [MSFT] via Microsoft SQL Server Development Customer Advisory Team on 11/15/2011 5:54:00 AM
As announced during the PASS Summit 2011 Day One Keynote, we are diving deeper into the world of Big Data by embracing and contributing to the open source community and Hadoop. We’ve had a lot of good coverage on this topic with some examples below. Microsoft Expands Data Platform With SQL Server 2012, New Investments for Managing Any Data, Any Size, Anywhere Microsoft makes its move with Hadoop on Azure and Windows Server Hortonworks, Microsoft Sign Co-Development Deal for Hadoop ...
by shauntj-us via Microsoft SQL Server Development Customer Advisory Team on 10/18/2011 2:06:00 AM
Author: Shaun Tinline-Jones Reviewers: Mike Ruthruff, Sanjay Mishra, Alexei Khalyako Not too long ago an ISV that developed solutions using SQL Server as the RDBMS, asked me how they could query a database as at a point in time. This was a relatively easy answer, thanks to the Database Snapshot feature. I was however surprised at the next question “Can we update the database snapshot?” A reactive response is “No. You cannot update a Database Snapshot” Msg 390 ...
by Denny Lee [MSFT] via Microsoft SQL Server Development Customer Advisory Team on 10/8/2011 9:05:00 PM
The SQLPASS 2011 summit is upon us again and all of our flocking to our fair city Seattle for our yearly technical-and-karoke fest! If you are up for some technical deep dives, here is the list PASS sessions presented by SQLCAT! And don’t forget to check out the Birds of Feather event – the tech talk luncheon. Session Code Session Name Speakers DBA-414-M Are You Smarter Than An MCM?
by Kun Cheng via Microsoft SQL Server Development Customer Advisory Team on 9/27/2011 2:55:00 AM
Author: Kun Cheng Reviewers: Thomas Kejser, Curt Peterson, James Podgorski, Christian Martinez, Mike Ruthruff Receive-Side Scaling (RSS) was introduced in Windows 2003 to improve Windows scalability to handle heavy network traffic, which is typically the case for SQL Server OLTP workload. For more details about RSS improvement on Windows 2008, please check out the whitepaper - http://msdn.microsoft.com/en-us/windows/hardware/gg463253.aspx and the blog - http://sqlcat.com/sqlcat/b/msdnm ...
by shauntj-us via Microsoft SQL Server Development Customer Advisory Team on 8/31/2011 4:09:00 PM
Author: Shaun Tinline-Jones Technical Reviewers: Thomas Kejser, Steve Howard, Jaime Alva Bravo, Kun Cheng, Jimmy May Note: Validation for this post was performed in the SQL CAT Customer Lab on an HP Proliant DL580 G7, Intel Xeon Nehalem E7-4870 2.40 GHz 4 socket, 10 physical cores, 20 logical cores for a total of 40 physical cores, 80 logical cores; 1TB RAM. SQL Server 2008 R2 was installed on a Fusion-io ioDrive Duo 1.28TB MCL using driver version 2.3.1 We recently engaged a Tier 1 Global ISV ...
by Denny Lee [MSFT] via Microsoft SQL Server Development Customer Advisory Team on 8/23/2011 6:02:14 PM
As noted in our blog post Announcing SSAS Maestros v1.2, we had a great Analysis Services Level 500 deep dive sessions in Redmond and Madrid back in June/July 2011. Saying this, we do have some quick updates on the SSAS Maestros current status: Annoucing the first set of SSAS Maestros: We have completed the evaluations of the v1.0 Maestros (Feb/Mar 2011) attendees and we will be notifying all of those attendees within the next few weeks - best of luck! SSAS Maestr ...
by ChuckHeinzelman via Microsoft SQL Server Development Customer Advisory Team on 6/24/2011 9:05:09 PM
Author: Chuck Heinzelman Reviewers: Kevin Cox, Dan Jones, Lara Rubbelke During a customer engagement, we noticed a query that was taking a long time to execute. We saw that the query had a scalar-valued User Defined Function (UDF) encapsulating functionality in the SELECT clause. To understand why this could be a problem, you need to understand how SQL Server deals with functions in the S ...
by Kevin Cox SQL Server via Microsoft SQL Server Development Customer Advisory Team on 6/20/2011 2:41:49 PM
Recently, a problem was presented to me by a DBA using SQL Server 2008 R2. He could not attach a database whose fileshad been copied from another server. This blog talks about the situation and the solution. Here are the steps he went through to arrive at his situation. DBA #1 logs into a remote server #1 using SSMS from his desktop and detaches a database. DBA #2 copies the files to SQL Server #2 and using SSMS on his desktop attempts to attach that database. DBA #2 gets an error s ...
by ChuckHeinzelman via Microsoft SQL Server Development Customer Advisory Team on 5/31/2011 6:24:31 PM
Author: Chuck Heinzelman Reviewers: Kevin Cox, Kun Cheng, Michael Thomassy In a recent customer engagement, I was presented with a problem that I have seen in the past and am surprised that I don’t see more often. Take the following table as an example: CREATE TABLE dbo.ResultSetTest ( ID integer IDENTITY(1,1) NOT NULL PRIM ...
by Denny Lee [MSFT] via Microsoft SQL Server Development Customer Advisory Team on 5/12/2011 12:42:00 AM
We are proud to announce that SQLCAT will continue with the SSAS Maestros course in June and July in Redmond and Madrid. SSAS Maestros 1.2 Courses Join us for a five-day deep-dive course on Analysis Services 2008 R2 UDM and join the SSAS Maestro Program. Prepared and presented by SQLCAT, top industry experts and the SQL Server Analysis Server team, this intensive 500-level course gives top SSAS professionals the education and hands-on experience needed to deliver highly complex and highl ...
by ChuckHeinzelman via Microsoft SQL Server Development Customer Advisory Team on 3/31/2011 3:59:00 PM
Author: Chuck Heinzelman Reviewers: Steve Howard, Carl Rabeler, Shaun Tinline-Jones, Mike Weiner, Murshed Zaman I was recently involved in a customer lab testing the Microsoft Business Intelligence stack. I noticed an interesting behavior when working on one of my test servers. Here’s the environment that I was working with: · Windows Server 2008 R2 (64-bit) · S ...
by Kevin Cox SQL Server via Microsoft SQL Server Development Customer Advisory Team on 3/8/2011 2:51:00 AM
Authors: Kevin Cox & Michael Thomassy Contributors: Lubor Kollar Technical Reviewers: Shaun Tinline-Jones, Chuck Heinzelman, Steve Howard, Kun Cheng, Jimmy May Overview Connecting directly to a SQL Azure database from a reporting tool (like Microsoft Excel and PowerPivot, or SQL Server Reporting Services) from your desktop or local data center is possible using a data source that looks like: [YourAzureServr@database.windows.net].[YourDatabase].[YourSchema].[YourTable] &n ...
by Kun Cheng via Microsoft SQL Server Development Customer Advisory Team on 3/3/2011 9:26:00 PM
Author: Kun ChengReviewers: Sunil Agarwal, Steve Howard, Shaun Tinline-Jones, Prem Mehra, Sanjay Mishra, Michael Thomassy, Mike Ruthruff, Howard Yin, Jimmy May During a recent ISV partner engagement, we decided to enable RCSI (Read Committed Snapshot Isolation) on a SQL Server 2008 R2 instance with the objective of minimizing blocking. As expected, we did not encounter any significant blocking. However, we did find different behavior when we compared the results using RCSI to those we got ...
by Denny Lee [MSFT] via Microsoft SQL Server Development Customer Advisory Team on 3/1/2011 4:15:50 PM
by Denny Lee As many of you know from the various blogs, whitepapers, and conferences from SQLCAT, there is a big obsession or compulsion toward I/O by various members of SQLCAT. If you’re not already familiar with this topic, definitely reference Mike Ruthruff’s whitepaper Predeployment I/O Best Practices. But as could be seen from the SSAS Maestros session in Redmond this week (for more information, check out What is the SSAS Maestros?), there is even an obsession for IOps even wit ...
by Sunil Agarwal via Microsoft SQL Server Development Customer Advisory Team on 2/20/2011 10:32:00 PM
Author: Sunil AgarwalReviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, And Kevin Liu Have you ever encountered a situation where a query was running fine yesterday but today it is running a lot slower? I am sure most of us have seen a situation like that which can be quite frustrating. While there can be many reasons but there are two common cases as described below: · First is that the query plan might have c ...
by Sunil Agarwal via Microsoft SQL Server Development Customer Advisory Team on 2/20/2011 10:24:30 PM
Author: Sunil AgarwalReviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, And Kevin Liu When a transaction T1 modifies a row, it takes an X lock on the row for the duration of the transaction. If another concurrent transaction T2 wants to modify the same row, it gets blocked waiting for T1 to complete. It is easy to understand why this blocking is necessary. SQL Server does not know the fate of T1 until it completes so it forces T2 to wait. However, there are ...
by Sunil Agarwal via Microsoft SQL Server Development Customer Advisory Team on 2/20/2011 10:20:40 PM
Author: Sunil AgarwalReviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, Kevin Liu Recently I was working with a customer who was surprised to see blocking when accessing a data row which was not locked? Let me describe the scenario using an example. Example: -- create a simple table for our examples create table basic_locking (c1 int, c2 int, c3 int) go -- insert 500 rows into the table declare @i int = 0 while (@i < 500)
...
by Sunil Agarwal via Microsoft SQL Server Development Customer Advisory Team on 2/20/2011 10:20:00 PM
Author: Sunil AgarwalReviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, Kevin Liu Recently I was working with a customer who was surprised to see blocking when accessing a data row which was not locked? Let me describe the scenario using an example. Example: -- create a simple table for our examples create table basic_locking (c1 int, c2 int, c3 int) go -- insert 500 rows into the table declare @i int = 0 while (@i < 500) begin & ...
by Sunil Agarwal via Microsoft SQL Server Development Customer Advisory Team on 2/20/2011 10:12:00 PM
Author: Sunil AgarwalReviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, Kevin Liu When using NOLOCK hint on the table or using read uncommitted isolation level, customers sometimes get surprised when they experience blocking. Let me explain this using the following example Example: Concurrent DDL operation -- create a simple table for our examples create table basic_locking (c1 int, c2 int, c3 int) go -- insert 500 rows into the table declare @i int = 0 wh ...
by Sunil Agarwal via Microsoft SQL Server Development Customer Advisory Team on 2/20/2011 9:13:00 PM
Author: Sunil AgarwalReviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, Kevin Liu In this series of blogs, I want to show some common concurrency questions that we get asked by customers. Before we look into common concurrency issues seen by customers, it will be good to review the transaction isolation levels provided in SQL Server. Isolation Levels: SQL Server supports all four isolation levels as defined in ANSI SQL standard. The ANSI standard does not dictate ...
by Mike Weiner - MSFT via Microsoft SQL Server Development Customer Advisory Team on 2/7/2011 11:01:39 PM
Wanted to make everyone aware of a whitepaper published on the SharePoint site but involving SQL Server and Remote Blob Store (RBS) integration with SharePoint. Authors were previous SQLCAT members Burzin Patel (now with StorSimple, who created the RBS provider mentioned in this paper) and Peter Scharlock Below is a summary and pointer to the complete paper. Enjoy! Summary: Microsoft® SharePoint® technology has seen an order of magnitude increase in its usa ...
by Kun Cheng via Microsoft SQL Server Development Customer Advisory Team on 12/21/2010 10:37:00 PM
Author: Kun Cheng Reviewers: Sunil Agarwal, Chuck Heinzelman, Shaun Tinline-Jones, Prem Mehra Although not documented very well, the system stored procedures listed below are known to many SQL Server developers and DBAs. For more details, please check out http://msdn.microsoft.com/en-us/library/ms176007.aspx. But rarely have any developers called these stored procedures directly from application code. They are usually executed on behalf of ODBC, OLE DB, or other APIs, which developers ...
by Peter Scharlock via Microsoft SQL Server Development Customer Advisory Team on 10/29/2010 10:14:27 PM
I was working with one of our ISV partners recently and they mentioned some very unusual SQL Server transaction log behavior. The transaction log size did not correspond to the actual size of the data being loaded into the database. The ISV created an empty database, setup a backup device, set the database recovery model to FULL, and then started loading data. They also intended to backup the transaction log occasionally to keep the log a manageable size. However, at the end of the load process ...
by Kun Cheng via Microsoft SQL Server Development Customer Advisory Team on 10/25/2010 11:03:00 PM
In a recent engagement with an ISV, we’ve run into a sporadic application failure problem when the application is deployed in a mixed environment. It comes down to finding out which version of SQL Server driver the application uses for connection. In the mixed environment, some application server machines have SNAC 9 (shipped with SQL Server 2005) and SNAC 10 (shipped with SQL Server 2008) installed side by side. And the application is designed to support both. It’s important to dete ...
by Thomas Kejser 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 ...
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 5 best practice documents to the TechNet Wiki: SQL Az ...
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 Thomas Kejser 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.
