SQL Feeds

SQL Server 2008 R2 UNICODE Compression – what happens in the background?

Peter Scharlock — Wed, 03 Mar 2010 00:06:00 GMT

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

Performance tips of using XML data in SQL Server

Kun Cheng — Mon, 01 Mar 2010 23:52:00 GMT

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

Full Text Indexing Terabytes of Files with SQL Server and Cloud Storage

Nicholas Dritsas — Wed, 03 Feb 2010 02:52:00 GMT

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

SQL Saturday #35 - Dallas

ErinW — Sun, 17 Jan 2010 18:09:00 GMT

I had intended to post this weeks ago. I guess I'm still postponing my New Year's resolution to stop procrastinating. We're organizing our first SQL Saturday event in Dallas on May 22nd, hoping to make it an annual event. It will be held at the Region 10 Education Center in Richardson, and we currently have 4 tracks slated: Development, Administration, Business Intelligence and Beginner. I'm particularly excited about the Beginner track since we so rarely find those at t

Monitoring free space in tempdb transaction log

tkejser — Mon, 14 Dec 2009 13:58:36 GMT

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

Performance Comparison between Data Type Conversion Techniques in SSIS 2008

tkejser — Tue, 01 Dec 2009 13:59:00 GMT

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

New TPC Results on SQL Server 2008 R2

Data & SQL Storage Performance Team — Mon, 30 Nov 2009 19:31:55 GMT

On November 2, 2009, Unisys published two new TPC results (TPC-E and TPC-H) using the newly announced SQL Server 2008 R2* and Windows Server 2008 R2**. These results were published on the 96-core Unisys ES7000 system. These are the first SQL Server TPC results using more than 64 cores and they demonstrate the outstanding scalability of the Windows and SQL Server platform. The TPC-E publication was 2,012 tpsE with a price performance of $958.23 USD/tpsE. The configuratio

Automating UBO

ErinW — Sun, 15 Nov 2009 16:14:15 GMT

I was preparing for my presentation at PASS and wanted to show some code for automating usage-based optimization. This is the process that allows for creating aggregation designs based on past query activity. I was surprised to find that there were no code examples posted on the web on how to do this, at least none that I could find. BIDSHelper and AggManager have similar functionality but, upon examining the code, they both use a more manual approach of examining each Dataset

How to View Currently Executing Queries in SQL Server

MarlonRibunal — Thu, 12 Nov 2009 20:14:38 GMT

Here’s a quick TSQL query to find the “currently executing queries” and the database name: SELECT db.name, er.session_id, er.transaction_id, er.start_time, er.[status], er.command, er.wait_time – etc. FROM sys.dm_exec_requests AS er JOIN sys.sysdatabases AS db ON er.database_id = db.dbid WHERE er.status = ‘running’ Here’s the query in action: Tagged: active queries, active query, active sessions, running queries, sql server 2008, sql server running

Selectively using secure connection to SQL Server

SQL Protocols — Mon, 19 Oct 2009 21:48:00 GMT

Secure connection to SQL Server can be enforced by different ways, e.g. forcing encryption by using “ForceEncryption” property under the Network Configuration in the server. But this setting will force all clients to use encryption and any client that is not able to use an encrypted connection will fail. If you need to connect to SQL Server selectively here is how to achieve this: You can take advantage of the two client settings, “Force Protocol Encryption” and “Trust Server Certificate” and tw