background preloader

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

When SSDs Make Sense in Server Applications In past posts, I’ve talked a lot about Solid State Drives. I’ve mostly discussed about why they are going to be relevant on the server side and the shortest form of the argument is based on extremely hot online transaction processing systems (OLTP). There are potential applications as reliable boot disks in blade servers and other small data applications but I’m focused on high-scale OLTP in this discussion. OLTP applications random I/O bound workloads such as ecommerce systems, airline reservation systems, and any data intensive application that does lots of small reads and writes, usually on a database where future access patterns are unknown. When sizing a server for one of these workloads, the key dimension is the number of small random I/Os per second.

TSQL String Array Workbench /* Robyn and Phil show how to use XML-based arrays to make string handling easier in SQL Server 2005/2008, and illustrate the techniques with some useful functions*/ /*Arrays aren't difficult in SQL Server 2005. Here's a very simple technique that can be extended to do some remarkably complex string processing. A while back, a friend was bemoaning the poor string handling of SQL Server. How to monitor deadlock using extended events in SQL Server 2008 and later - Microsoft SQL Server Tips & Tricks Before I talk about how to monitor the deadlocks let us have a small brief what is deadlock and how we were handling them in the previous versions of SQL Server. Deadlock: A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. How SQL Server resolves a deadlock? In SQL Server Deadlock can be detected and resolved automatically without manual intervention. Here in SQL Server, Deadlock Monitor (Basically a Lock Monitor) scans through the Processes or threads every 5 seconds and if any such scenario exits, it terminates the query batch involved in that processes based upon cost of resources used.

NOSQL Patterns Over the last couple years, we see an emerging data storage mechanism for storing large scale of data. These storage solution differs quite significantly with the RDBMS model and is also known as the NOSQL. Some of the key players include ...GoogleBigTable, HBase, HypertableAmazonDynamo, Voldemort, Cassendra, RiakRedisCouchDB, MongoDB These solutions has a number of characteristics in commonKey value storeRun on large number of commodity machinesData are partitioned and replicated among these machinesRelax the data consistency requirement. (because the CAP theorem proves that you cannot get Consistency, Availability and Partitioning at the the same time)The aim of this blog is to extract the underlying technologies that these solutions have in common, and get a deeper understanding on the implication to your application's design.

SQL String User Function Workbench: part 2 /* In which Robyn and Phil pull together the themes from their TSQL String Array Workbench and String User Function workbench, to provide a simple TSQL string-handling package. */ This workbench finishes of what has been a three-part series of string functions. In it, we introduce the idea of using XML to provide a very simple array for doing string handling. This allows us to use functions for searching and splitting strings that will be familiar to users of procedural languages such as PHP and Python. Prevent SQL Server Reporting Services Slow Startup Problem SQL Server Reporting Services 2008 (SSRS 2008) starts up slowly after a period of inactivity when I try to access the reports page. Sometimes it's slow accessing it just half an hour later, sometimes hours later, or next day, but I can't figure out why or the pattern behind it? Check out this tip to learn more. Solution

SQL Server 2008 MCM Readiness Videos The Microsoft Certified Masters Program for Microsoft SQL Server provides the most experienced and talented IT Professionals with worldwide validation and recognition of their deep technical expertise in SQL Server. The SQL Server 2008 Microsoft Certified Master program has been re-launched in a more scalable format because of the increased interest and global demand for the program. For more information, see the Microsoft Learning SQL Server 2008 Microsoft Certified Master program site.

URLEncode Hello Robert Cary, Thank you for the cool TSQL URLEncode script, it was almost exactly what I was looking for. I slightly modified it to allieviate the need for creating/populating table numbers by selecting from a derived table using RowNum() as the incremented field [num] Alzowze ";0)ps... Solving issue with first long starting report on SSRS 2008 » Pavel's SQL Pages As I wrote in my previous blog post First report and report after specific time interval starts a long time on MS SQL 2008 Reporting Services, a first report after specific time takes very long to start. The issue is caused by the way how SSRS works and SSRS regularly restarts application domain after specific time period. After the application domain is restarted, then upon first request to the SSRS it needs to load all the settings and it takes quite a long time. There is no real solving to the issue except increasing the interval between the application domain restarts from default 720 minutes to other value which meets your business needs more closer.

Why SQL Server runs best on Microsoft Azure - SQL Server Team Blog Microsoft Azure Infrastructure Services offers many ways to optimize your on-premises data and data platform projects from development and test of new SQL Server applications to migrating existing on-premises SQL Server instances on the latest images of SQL Server to cost effective hybrid scenarios such as database backup and extended business continuity. So why is Microsoft Azure Infrastructure Services the ideal place to implement such scenarios? The latest Images of SQL Server always available Let’s start with having the latest images of SQL Server as soon as they are made generally available including tuned images for select workloads like an optimized data warehousing image. An example is Microsoft Azure offered SQL Server 2014 CU1 update at the end of April, the day it was made generally available. This image is still pending to be uploaded on many other service providers.

Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other’s object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process.

Parameter Sniffing, Embedding, and the RECOMPILE Options Parameter Sniffing Query parameterization promotes the reuse of cached execution plans, thereby avoiding unnecessary compilations, and reducing the number of ad-hoc queries in the plan cache. These are all good things, provided the query being parameterized really ought to use the same cached execution plan for different parameter values. Photo Gallery: SQL Server Hybrid Cloud Options Without a doubt, SQL Server DBAs and database professionals are among the most reluctant customers to adopt cloud anything, and there are some good reasons for that. SQL Server is a business critical infrastructure component that most organizations simply won’t trust to the cloud. Those organizations have a significant investment in on-premises hardware and software, plus moving to the cloud involves a number of risks.

Development content from SQL Server Pro New in SQL Server 2008 is support for large user-defined types (UDTs). We’re going to build a Common Language Runtime (CLR) UDT that allows us to store, compress, and decompress binary data (up to 2GB in size) inside SQL Server 2008. This technique works only in SQL Server 2008 because only SQL Server 2008 allows CLR UDTs larger than 8KB. The UDT we’ll build, called VarBinaryComp, accepts both uncompressed data (which it will compress before storing) and compressed data as input. It also has properties to determine the length of the compressed and uncompressed data.