Aggregated Posts (sorted by date)
Database Copy Command & Handling Asynchronous Execution in T-SQL in SQL Azure
Abstract from TSQLNet Weblog - Cihangir Biyikoglu (Indexed 2010-09-01):
Database Copy T-SQL command in SQL Azure, provide the ability to create a identical copy of a database through a pure server side process. You can think of it as a command that backs up a database and restores it at the other end. You can refer to general documentation for the details and use cases. Database Copy command works on any size database thus in general require a long transaction. Just like the SQL Server Backup command, the operation takes a while and create a point in time consistent...(truncated)...
Slow query performance because inaccurate cardinality estimate when using anti-semi join following upgrade from SQL Server 2000
Abstract from PSS SQL Server Engineers (Indexed 2010-09-01):
We have a few customers who reported that some of their queries run slower following upgrade from SQL Server 2000 to SQL Sever 2005, 2008 and 2008 R2. Specifically, queries experiencing the issue have anti-semi joins in the query plan and the join involves multiple columns as joining condition.Anti-semi joins are results of query constructs like NOT EXISTS, NOT IN. Here is an example of the query that would result in anti-semi join:SELECT t1.* FROM tst_TAB1 t1 WHERE NOT EXISTS( SELECT * ...(truncated)...
Optimizing CREATE SET in Cube Calculation Scripts
Abstract from MS SQL Server Development Customer Advisory Team (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)...
The New World of Work: Unique Tasks
Abstract from Carpe Datum (Indexed 2010-08-26):
In a previous post I mentioned that I’ve seen some Gartner Inc. studies, among others, that state that we’re in a “new world of work”. Since I’ve been working in technology for quite some time, and working around the world for longer, I agree that things are not what they were in the 9-5, Monday through Friday, pre-defined, single-employer world of yesterday. Things are far more dynamic, and include emerging work methods like the “swarms” I mentioned, an...(truncated)...
SQL Server 2005: Execution Context
Abstract from Laurentiu Cristofors blog (Indexed 2010-08-25):
This post is based on an old presentation I gave several years back. A video of the presentation used to be available here, but today I couldn't get it to work, so I am attempting to make available most of the information from the presentation within this post. Keep in mind that the demo associated with this presentation is also available here. And some of these concepts may have been covered in more detail elsewhere on my blog:Basic SQL Server Security concepts - logins, users, and principalsBa...(truncated)...
Query performance and table variables
Abstract from PSS SQL Server Engineers (Indexed 2010-08-24):
Technorati Tags: Performance Frequently, we see our customers using table variables in their stored procedures and batches and experience performance problems. In general, these performance problems are introduced because of large number of rows being populated into the table variable. Table variables were introduced in SQL Server 2000 with intention to reduce recompiles. Over time, it gained popularity. Many users use to to populate large number of rows and then join...(truncated)...
Why use SQL Server 2008 R2 BPA? Case 1: Missing Updates..
Abstract from PSS SQL Server Engineers (Indexed 2010-08-24):
In June I introduced you to a new Best Practices Analyzer for SQL Server, SQL Server 2008 R2 BPA: http://blogs.msdn.com/b/psssql/archive/2010/06/20/introducin...(truncated)... Ive seen some mixed reaction to this tool. But when I verbally talk to some about the type of knowledge CSS has put into the rules, they see a greater value. Therefore, I thought I would put together a series of blog posts with some examples of this knowledge. The first is something I call Missing Updates. While Microso...(truncated)...
A combination of crowdsourcing, people helping people, and the best technical community there is
Abstract from Carpe Datum (Indexed 2010-08-19):
I talk a lot about “giving back”. It’s a personal issue with me – I grew up quite poor, and from time to time someone would take notice that my mom and I didn’t have enough to eat, and they would help us out. I’ve never forgotten those folks. So whenever I can, I try to help others out as well. And I’ve found that the folks that work with SQL Server have that same spirit. My good friend Arnie Rowland down in Oregon has started an amazing project. Backed ...(truncated)...
How It Works: Timer Outputs in SQL Server 2008 R2 - Invariant TSC
Abstract from PSS SQL Server Engineers (Indexed 2010-08-18):
[Image] I would love nothing more than to take you back to my high-school days running the 440 yard hurdles (yes yards not meters) where timing was done with a stop watch (you know the old, moving dial style) but timers on the PC don't allow that simplicity. I have discussed the timing behavior is SQL Server in previous blogs and it is time to discuss timing behaviors again as SQL Server 2008 R2 has been updated. How It Works: SQL Server Timings and Timer Output (GetTickCount, timeGetTim...(truncated)...
Direct Contact with SQL Server's Future
Abstract from Carpe Datum (Indexed 2010-08-17):
Many times I'm asked "does Microsoft ever ask the users what they want in SQL Server"? Yes, we do. A lot, actually. And you can be a part of that conversation. For starters, you can hit the http://connect.microsoft.com site, read up on how it works, and submit bugs (problems you've found in the software) or features that you want. No, it's not a perfect system, but it's more than I've seen at most software vendors I deal with. You'll be asked do a search (it's a pain, but a necessary one) to see...(truncated)...
How It Works: Enumeration of sys.messages
Abstract from PSS SQL Server Engineers (Indexed 2010-08-16):
I ran into an issue which has some aspects I found interesting and thought you might as well. When you do a select against the sys.messages virtual table the data is retrieved from the resource files (.RLL) files stored under BinnResource directory. This is done by loading the RLL library and retrieving the resource string information then materializing the row in the result set. What I found interesting is that by default SQL Server will materialize a row for each of the installed resource la...(truncated)...
Microsoft SQL Server 2008 R2 Management Pack is now available
Abstract from SQL Server Manageability Team Blog (Indexed 2010-08-13):
We've made some important improvements and investments in the SQL Management Pack for System Center, and the English language version just RTW-ed last week. You can read more about it here on the Release Services Team's blog. Keep an eye on that blog...(read more)[Image]
New Release of SQL Server Migration Assistant (SSMA) includes migration from MySQL and to SQL Azure
Abstract from MS SQL Server Development Customer Advisory Team (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)...
Migration Assistant Released Going from MySql and Access to SQL Azure just got much easier
Abstract from TSQLNet Weblog - Cihangir Biyikoglu (Indexed 2010-08-13):
I talked about this tool in previous postings. SSMA Mysql and Access just got released with SQL Azure support. Full details available here; http://blogs.technet.com/b/dataplatforminsider/archive/2010/...(truncated)... Enjoy. -Cihangir Biyikoglu Program Manager - SQL Azure[Image]
Windows 7 AppCompat changes and SQL Server client and setup hangs
Abstract from SQL Protocols (Indexed 2010-08-13):
Windows 7 and Windows Server 2008 R2 include many changes to the core operating system. This blog post is targeted at resolving the impact on SQL Server client drivers and SQL Server setup of one breaking change in Windows 7 and Windows Server 2008 R2.The changes to behavior of the GetOverlappedResult API, which SQL Server client drivers use for performing network reads and writes, is described here: http://msdn.microsoft.com/en-us/library/dd371711(VS.85).aspx. When SQL Server client drivers get...(truncated)...
Replay Result Set Event (Replay * Events)
Abstract from PSS SQL Server Engineers (Indexed 2010-08-13):
From: Robert Dorr Sent: Friday, August 13, 2010 8:58 AMSubject: RE: SQL Server Trace Replay - "Replay Result Set Event" The Result Set event is one of several client side replay events. Here are some of Common Result Event ColumnsTextReturned values for example DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud'- RETURNS - IsCloud as column name in result set event IsCloud = 0 as value i...(truncated)...
Watch those clustered indexes
Abstract from Carpe Datum (Indexed 2010-08-11):
I had a discussion with a friend in Microsoft IT this morning, and as we were evaluating some query plans we ran into more clustered index woes. Remember that clustered indexes affect the base table itself (which is why there can be only one) and are expensive to update. They are fantastic for reading data, but you need to carefully evaluate if they meet the criteria for having an efficient use of this type of index. More here: http://www.sqlskills.com/BLOGS/PAUL/category/Indexes-From-Ev...(tru...(truncated)...
Vardecimal storage format and upgrade to SQL2008 RTM or SQL2008R2
Abstract from SQL Server Storage Engine (Indexed 2010-08-10):
As you all may know that starting with SQL2008 RTM, we have deprecated vardecimal storage format feature. The reason is that ROW compression, introduced starting SQL2008 RTM extends the variable storage format to other data fixed length types such as INT, CHAR, DATETIME including DECIMAL datatype. This deprecation is aligned with the standard deprecation policy of SQL Server. This means that when you upgrade from SQL2005/SP2 to SQL2008 and you are using vardecimal storage format, it will just wo...(truncated)...
ISV Applications versus In-House Applications
Abstract from Carpe Datum (Indexed 2010-08-10):
I’ve written a series on creating an “Application Profile” for your organization, which allows you to create a strategic plan on everything from Business Continuity to Disaster Recovery, but many of us just don’t have the time to do that much work. But that doesn’t mean you shouldn’t do *something*. There is one very important bit of information to have – the names of the applications that run against your SQL Servers and who wrote them. Versions would ...(truncated)...
SqlDependency based caching of LINQ Queries
Abstract from Service Broker Conversations (Indexed 2010-08-04):
Query Notifications is the SQL Server feature that allows a client to subscribe to notifications that are sent when data in the database changes irrelevant of how that change occurs. I have talked before about how Query Notifications works in the article The Mysterious Notification. This feature was designed specifically for client side cache invalidation: [...]