Welcome to Database Weekly

ID Generation in Federations in Azure SQL Database: Identity, Sequences, Timestamp and GUIDs (Uniqueidentifier) Identity and timestamp are important pieces of functionality for many existing apps for generating IDs. Federation impose some restrictions on identity and timestamp and clearly we need alternatives for federations that can scale to the targets of scale federations hits. So I’ll dive into alternatives and options in this post. read more... SQL Server Tricks: How Can I Restore a Subset of My Tables to A Prior Point in Time? This question came up when working with a client last week. The team mentioned they were planning to move some tables into a new filegroup. The new filegroup is intended to help quickly restore data in cases when a monthly process goes off the rails and leaves the data in some tables in a bad state. read more... Video: Heaps in SQL Server: Performance, Maintenance, and Your Sanity Do you use heaps in SQL Server? How can heaps impact your query performance and database maintenance? In this 30 minute webcast I will show you how heaps behave differently than than tables with clustered indexes in SQL Server. You’ll learn how to identify heaps, how to measure their size, and how to plan a change to convert your heaps to clustered indexes. read more... Dell 12th Generation Server Cheat Sheet Over the past several months, Dell has been rolling out a number of new 12th generation servers that all use the new 32nm Intel Xeon E5 series processor (aka Sandy Bridge-EP). These new servers all have much higher memory density and more PCI-E slots (which are also PCI-E 3.0) compared to the older 11th generation Intel based servers that used the 32nm Intel Xeon 5600 series processor (aka Westmere-EP). These new servers are a huge improvement over the previous models. read more... Ad hoc query optimization in SQL Server When ad hoc queries are executed in sql server, if it is executed without parameters, and it is simple, SQL Server parameterizes the query internally to increase the possibility of matching it against an existing execution plan, that's called "Simple Parameterization". read more... Shredding a simple xml structure to a single row flat table In the past I have used the XMLTable function developed and blogged by Jacob Sebastian to shred an xml structure and suggested this as a starting place for them. After posting that comment, it did get me thinking though that I could use the XMLTable function by Jacob to shred any simple xml structure to a table and then pivot the results to a single row table and as such came up with something fairly quickly and added that to the thread. read more... SQL Server 2012 Engine: Express LocalDB–Command line Express LocalDB is an express edition with minimum files required to start sql server for developers to work. This is only for developers assuming they donot have much knowledge of DBA and do not want to know much, they are more focus on their localDB to work on with. read more... AlwaysON - HADRON Learning Series: Worker Pool Usage for HADRON Enabled Databases I am on several e-mail aliases related to Always On databases (reference Availability Group, AG, HADRON) and the question of worker thread usage is a hot topic this week. I developed some training around this during the beta so I decided to pull out the relevant details and share them with you all. Hopefully this will provide you with a 10,000 foot view around the basic worker thread consumption related to HADRON enabled databases. read more... Thinking about Deprecated, Discontinued Features and Breaking Changes while Upgrading to SQL Server 2012 Change is the only constant in this world. Therefore, whenever customer requirements, newer architectures and designs require software vendors to make a change to the keywords, functions, etc; they ensure that they provide their end users sufficient time to migrate over to the new standards before dropping off the old ones. Microsoft does that too with it’s Microsoft SQL Server product. read more... Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN I've blogged a bunch about using the undocumented fn_dblog function I helped write (and I've got a lot more to come :-) but here's one I haven't mentioned on my blog before: fn_dump_dblog. read more... DACFx 3.0: The new programming API Looking at the API and at the serialized form (i.e. the DACPAC), it turns out that DACFx 3.0 is not just "DAC V-next". It's an entire quantum change from all other versions, including DAC 2.0, the version that introduced the BACPAC (serialized schema and data). read more... Kanban: The Secret of High-Performing Teams at Microsoft A Kanban is a simple project management tool. It enables you to visualize your workflow, limit your work in progress, and optimize your “cycle time” (the time it takes to complete one item.) For software development projects, this is a big deal. It helps you find bottlenecks and push quality upstream. Ultimately, you shape your process to flow more value as efficiently and effectively as possible, “just in time.” Another way to think of it is, your users “pull” value through your development chain, while you streamline your process. read more... Big Data.. Big Opportunity Last week I attended The 2012 Eduserv Symposium. The event was focused on 'Big Data' and discussed whether Big Data represents a challenge or an opportunity and how we can best make use of it. read more... Tuning Azure Performance, a brief introduction One common reason we go for cloud computing is the ability to scale, as much as needed. This usually means an increase in the overall performance of our application. However in certain cases we can actually face a decrease in performance with cloud computing. I will try to explain the most common reasons that I came across for such unexpected behaviour. read more... One Way To Insert Many Rows Very Fast From Your .NET Application Recently, I was asked to troubleshoot the startup performance of a VB.NET application with a SQL 2008 back-end. The startup routine was taking up to a minute or so to complete in locations with a fast DB connection, and up to 15 minutes in locations with a slow DB connection. read more... Use Powershell script to verify backup files Here is a little Powershell Script which can help you verify the backup files, and export the results to file. read more... CDC for in SQL 2012 The is one the best little known features for 2012. If you have a data source that is either SQL or Oracle now you no excuse not to do CDC. Along with CDC I would highly recommend give you ETL the ability to do good old fashion RBAR (row-by-row) delta detection. read more... Analysis Services Processing and CXPACKET Waits Performance tuning – my favorite! This blog originated from a quest to reduce the processing time of an SSAS cube which loads some 2.5 billion rows and includes DISCINTCT COUNT measure groups. The initial time to fully process the cube was about 50 minutes on a dedicated DELL PowerEdge R810 server, with 256 GB RAM and two physical processors (32 cores total). Both the SSAS and database servers were underutilizing the CPU resources with SSAS about 60-70 utilizations and the database server about 20-30 CPU utilization. What was the bottleneck? read more... SQL SERVER – Columnstore Index and sys.dm_db_index_usage_stats As you know I have been writing on Columnstore Index for quite a while. Recently my friend Vinod Kumar wrote about SQL Server 2012: ColumnStore Characteristics. A fantastic read on the subject if you have yet not caught up on that subject. After the blog post I called him and asked what should I write next on this subject. He suggested that I should write on DMV script which I have prepared related to Columnstore when I was writing our SQL Server Questions and Answers book. read more... New Intel Xeon E5 Processors: ZT Systems, Dell, SGI Intel announced an expansion of its Xeon processor portfolio with new products designed to address a broad and emerging set of server requirements and provide IT managers with additional choice and flexibility. read more...