Clustered Index On UniqueIdentifier Column

Clustered_Index_On_UniqueIdentifier

Hello all,

It’s been a year since we posted a blog. It’s been the busiest year of my life. However, we strongly believe that learning is a journey and it should be a continuing process. Coming to the point, recently I had to handle a customer problem with the overall application performance. Found some interesting problems and thought of sharing the same.   

 

Clustered Index on UNIQUEIDENTIFIER column

 

Problem Statement:

Clustered Index on UNIQUEIDENTIFIER column takes you through high-level problems with the GUID. We have a crucial database environment where we were facing some performance issues. We identified a few issues fixed them. While analyzing the overall database architecture, we observed a few interesting facts which are highly impacting the database performance. One of the major observations is having a clustered index on UNIQUEIDENTIFIER column on Transactional tables where we are seeing a lot of writes on those tables. When we captured the statistics below are the observations:

1. Index Rebuild is happening on Sunday afternoon

2. Index fragmentation (Index on UNIQUEIDENTIFIER) is reaching 65% by Monday afternoon

3. We do not get any maintenance window during the week as the application is running in 24X7

4. We are using the same fragmented index during the week till Saturday

5. On that clustered index Bad Page Splits Per Second is high > 38% of Batch Requests Per Sec

6. These indexes are being highly used across the application for both WRITES and READS

An expert from the customer side asked us to brief our analysis and the generic information on why clustered index on UNIQUEIDENTIFIER is creating the problem and the possible solutions. Here is our analysis and the solution suggested.

Continue reading

Posted in Database Design, Performance Tuning, SQL Development | Tagged , , , , | 2 Comments

Latest SQL DBA Interview Questions and Answers

Latest SQL DBA Interview Questions and AnswersThe post Latest SQL DBA Interview Questions and Answers takes you through the latest SQL DBA interview experiences shared by blog followers. You can also share your interview experience and we can provide you the answers:

Q. Can we perform a log backup with COPY ONLY option?

Ans:

Yes Of course we can perform Copy Only log backups

Q. What are the marked transactions in SQL Server?

Ans:

When we are dealing with related databases which means there is a dependency between databases and we need to establish a recovery strategy for all databases to the same consistent phase. But with this approach we may lose the recently committed transactions. Marking transaction logs is beneficial when you are dealing with multiple databases and want to restore all of them to the same point in time recovery.

This is used in rare cases and suitable for TFS because when there is disaster ‘occurs we need our data back in all databases to the same consistent manner, if you don’t mark the T-Logs and restore using the normal point in time recovery it might get our data back but with inconsistency between databases. For example I have created a new branch in TFS for new development work and and somebody checking and modifying code this total activities are recorded in multiple databases so when we want to restore it back we should get exactly the same consistent point. Have a look at here:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/use-marked-transactions-to-recover-related-databases-consistently

Q. Why Primary key is mandate for Transaction Replication?

Ans: Continue reading

Posted in Interview Q&A, SQL Server DBA | Tagged , , , , , , | 1 Comment

Learning Microsoft Power BI Lesson 4

Learning Microsoft Power BI Lesson 4Learning Microsoft Power BI Lesson 4 – Exploring Power BI Service

The post “Learning Microsoft Power BI Lesson 4” takes you through the over view and interacting with Power BI Service. We have created an account at Office 365 and using to log into Power BI Service:

Log into Power BI service: https://powerbi.microsoft.com/en-us/

Learning Microsoft Power BI Lesson 4 Exploring Power BI Service

We’ll explorer Power BI service with the Sample Datasets available:

Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4

The moment you connect to the “Sales and Marketing” dataset, it connects to the sample dataset and creates a Dashboard and report with a set of visuals as shown in below:

Learning Microsoft Power BI Lesson 4

We can observe various things from below Screen:

  1. Hide / View Navigation Pane
  2. Available Workspaces
  3. Enter Full Screen Mode
  4. Viewing the report in Web view mode
  5. Viewing the report in Mobile view mode

Learning Microsoft Power BI Lesson 4

Let’s view the report in mobile mode:

Learning Microsoft Power BI Lesson 4

Power BI automatically creates Dashboard and Report using the sample datasets:

Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4

From the default report created from sample dataset we’ll prepare a new dashboard by adding specific visuals from each page:

Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4

Go back to My “Work Space” and from Dashboards we can see the newly created Dashboard

Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4

Summary:

  • We have interacted with the Power BI Service interface
  • Connected to sample dataset and observed how reports and dashboards are created
  • Created “MyFirst_Dashboard” using the visuals / tiles from the existing report

Power BI Lesson 1

Power BI Lesson 2

Power BI Lesson 3

Power BI Introduction

Posted in MSBI, Power BI, SQL Development | Tagged , , , , , , , | 5 Comments