Group items tagged

"Every couple of weeks on one of the forums someone will ask a question about how to restore a backup from SQL 2008 to SQL 2005. The answer to the question is always, you can't restore a backup to a lower version of SQL Server. If you need to migrate backwards it is an entirely manual process. I learned this unfortunate truth a few years ago after upgrading a development server to SQL Server 2005 ahead of upgrading the production server for testing by the developers of our eCommerce application. At the time there was a new database for another web project in development that didn't exist in production, which was on SQL 2000 still, and when I had to move it to QA, also on SQL 2000 still, what was originally planned as a backup/restore turned into a "What do I do now?" moment."

"I guess this could be considered a continuation of my last post about SQL Server Memory, Understanding the VAS Reservation (aka MemToLeave) in SQL Server, but for a long time I have noticed that when people don't understand how SQL Server uses memory on their server, they immediately begin to think that SQL Server has a memory leak. So in this post I'll dive into how SQL Server allocates and uses the physical memory available on a server."

"As part of my talk at the 2009 US PASS Summit here in Seattle called Inside SQL Server Wait Types, I'm creating this blog post as a reference point that can be used to find out information about wait types in SQL Server 2005 and 2008. My hope is that if you have a question about a wait type you encounter with SQL Server 2005, 2008, or beyond, you will use this blog post as the starting point. This blog post should also be used as an enhancement to what you can find at the following SQL Server Books Online topic:"

"DBAs and developers alike find it hard to effectively develop, deploy and manage a data-tier application throughout its lifecycle. SQL Server developers today want to create a data-tier application they can test, declaratively deploy across multiple instances while the system deals with version upgrades. SQL Server DBAs on the other hand want to know deployment requirements a priori and they too want the system to perform some of the mechanical upgrade operations instead of managing data-tier applications via a bunch of scripts. In this video we will dive into Microsoft's investments in the SQL Server data-tier application space and demonstrate the concepts and tools that simplify development and deployment of data-tier applications using Visual Studio 2010 and SQL Server Management Studio in SQL Server 2008 R2."

"The Crimson Consulting Group has just published a white paper worth reading: Comparing the Business Value of Microsoft SQL Server 2008 R2 and Oracle Database 11g, Perceptions vs. reality in the RDBMS marketplace (April, 2011). The traditional user-friendliness of a mid-tier product has matured in terms of scalability. Today, you will find SQL Server 2008R2 installations running on 256 core machines -- scalability is no longer an issue. Unfortunately, the traditional scalability product has not grown significantly friendlier to developers and users; this leaves SQL Server 2008R2 the clear value winner for both lowest development expense and total cost of ownership for operations."

"Microsoft SQL Server performs dynamic memory management based on the memory requirements of the current load and activities on the system. On a Windows Server 2003 or a Windows XP or later version system, SQL Server can use the memory notification mechanisms that are provided by the QueryMemoryResourceNotification Windows API. On a Microsoft Windows 2000 Server-based system, SQL Server periodically calculates the free physical memory on the system by using the native Windows API. Based on this information from the QueryMemoryResourceNotification Windows API or from the memory calculation, SQL Server responds to the current memory situation on a specific system. This provides the following benefits:"

"Lock escalation is a event which occurs when SQL Server decides to upgrade a lock at a lower level hierarchy to a lock to a table level lock., In other words, when a particular query obtains a large number of row level locks/ page level locks, SQL Server decides that instead of creating and granting number of row level/page level locks, it is effective to grant a single table level lock. Or to be precise,"

" SQL Sentry Plan Explorer is a FREE tool that builds upon the graphical plan view in SQL Server Management Studio (SSMS) to make query plan analysis more efficient. It is a lightweight standalone app that contains many of the plan analysis features introduced in SQL Sentry v6. It does not require a collector service or database. "

"If a SQL query has parameters, SQL Server creates an execution plan tailored to them to improve performance, via a process called 'parameter sniffing'. This plan is stored and reused since it is usually the best execution plan. Just occasionally, it isn't, and you can then hit performance problems, as Greg Larsen explains."

"Many experienced database professionals have acquired a somewhat jaded view of parallel query execution. Sometimes, this is a consequence of bad experiences with older versions of SQL Server. Just as frequently, however, this view is the result of misconceptions, or an otherwise incomplete mastery of the techniques required to effectively design and tune queries for parallel execution."

"Resource Governor is a new feature and one of the most anticipated management features in SQL Server 2008. It bolsters SQL Server performance by allowing DBAs to establish resource limits and priorities on processor and memory-related workloads and resource pools. By defining resource limits on resource consumption, it is possible to prioritize multiple workloads in order to ensure consistent performance and maximize the experience of the end users and the efficiency of the applications without degradation."

"Lurking on any machine that has Windows Scripting Host installed (virtually all machines these days, although scripting can be disabled), is a powerful Regular Expressions facility, the VBScript.RegExp scripting object. You can get to it from any COM client that supports the IDispatch interface. IDispatch, you'll recall, is COM's popular late-binding interface - it allows applications to use COM components without knowing anything about them at compile-time. In T-SQL, we get to IDispatch via the sp_OA stored procedures. Via a simple UDF, we can access the RegExp object as though it were part of T-SQL:"

Free "SQL Server Statistics" eBook and a trial of SQL Monitor

"To say I like SP_WhoIsActive is an understatement. This is probably the most useful and effective stored procedure I've ever encountered for activity monitoring. The purpose of the SP_WhoIsActive stored procedure is to give DBAs and developers as much performance and workload data about SQL Server's internal workings as possible, while retaining both flexibility and security. It was written by Boston-area consultant and writer Adam Machanic, who is also a long-time SQL Server MVP, a founder of SQLBlog.com, and one of the most elite individuals who are qualified to teach the Microsoft Certified Master classes."

"Takeaway: SQL Server 2005 has several system tables and views that can provide a ton of insight into blocking problems that you may be facing in your database environment. Through these procedures, you can find blocking chains. Learn more about blocking processes."

"In SQL Server 2008 Microsoft has introduced a number of new date and time data types. One of these is the datetimeoffset data type. This data type includes an offset from UTC time as well as the datetime value and ensures that the datetime can be retrieved in UTC or a particular timezone based on this offset. There are also new functions to allow for conversions between different time zones using the new function SWITCHOFFSET(). "

"The basic texts for developing SQL code tend to leave unsaid the basic techniques for building routines such as stored procedures in TSQL. Phil is well-known for his more lengthy and complex stored procedures, so we asked him to explain in more detail how he goes about developing things like that without the comfort of Visual Studio "

" Are you tired of fighting with overly complex ORMs such as Entity Framework and NHibernate? Are you tired of poorly generated SQL or having to change your business objects to work with your ORM? Do you miss the power and performance of ADO.NET and SQL, but not the manual tedious work? If so FluentData might be the framework for you."

Dapper - a simple object mapper for .NetOfficial Github clone: https://github.com/SamSaffron/dapper-dot-net FeaturesDapper is a single file you can drop in to your project that will extend your IDbConnection interface. It provides 3 helpers: Execute a query and map the results to a strongly typed Listpublic static IEnumerable Query(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

"A simple user interface to automate the batch processing of comparing Sql Server table data using the tablediff.exe utility that comes with Sql Server 2005/2008. "