What size is suitable as an Initial Size for tempdb system database?

The size and physical placement of the tempdb database can affect the performance of a system. For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with auto-growing tempdb to the size required to support the workload every time you restart the instance…

2

Basic steps to setup Windows Server 2012 Cluster with Hyper-V

Setup Windows Server 2012 Cluster in Hyper-V iSCSI Target Configuration iSCSI Initiator Configuration Setup Windows Server 2012 Cluster There are different types of Quorum models. Nodes and Disk Majority type will be used fir this example. Pre-Requisites 1 Windows Server 2012 server (It will be called as DC01 in this example) 2 Windows Server 2012…

3

How to identify and solve the fragmentation in SQL Server?

How does fragmentation occur in SQL Server? If there is an index on the object, the data is ordered in the page using the slot array. When a new insert comes, SQL engine will search an available page to keep the data by using the index. The data should be saved in the specific page…

2

Which one is better? PERSISTED Column or XML Indexes?

I had a chance to work on the XML area. The customer had a performance issue with some queries which were related to XML data. The customer was using a banking application called TEMENOS T24 on Microsoft SQL Server. The tables in the databases have just 2 columns RECID and XMLRECORD which has all the…

0

How to find busy tables in SQL Server?

There is a performance issue on the system. You check the sys.dm_os_wait_stats and you see that PAGEIOLATCH wait type is at the top. You know that it is related to disk performance. It may be because of the high IO issue or disk related issue itself. You collect the performance counters “Avg. disk sec/Read” and…

3

Guest user account in SQL Server

It is recommended to disable guest user in every database as a best practice for securing the SQL Server. Guest user permits access to a database for any logins that are not mapped to a specific database user. The guest user cannot be dropped but it can be disabled by revoking the CONNECT permission. The…

2

Autogrowth option for SQL Server database files

It is not recommended to keep “In Percent” File Growth option for database files. If “In Percent” option is used for any database file and if the file size is very big, it may cause performance degradation during the file growth phase. It may not be a very big problem if “Instant File Initialization” is…

11

SQL Server Builds

How to identify SQL Server version Open SQL Server Management Studio and run of the following queries. First Query: SELECT SERVERPROPERTY(‘productversion’),        SERVERPROPERTY(‘productlevel’),        SERVERPROPERTY(‘edition’) Second Query: SELECT @@VERSION To view all versions click the link [more] Builds SQL Server 2012 SQL Server Version Builds Released On Download RTM 11.0.2100.60 RTM + CU1 11.0.2316.0 April…

0

Using Distributed Replay to load test your SQL Server–Part 2

In the first part of this series, I explained you how to install and get started with Distributed Replay. I continue in this series by showing you how to actually capture and replay workloads. After we have configured the Distributed Replay as explained in the previous post, we can use the Distributed Replay environment to…

1

Using Distributed Replay to load test your SQL Server–Part 1

This is the first part of a two part series where I explain how to use the new Distributed Replay functionality in SQL Server 2012. The first part covers setup and the second one covers the usage. Have you decided to migrate SQL Server to a newer version? Would you like to test the load…

0