Configuration – DB Cloud TECH

November 24, 2017September 15, 2018 Ayman Elnory Administration, Configuration, Performance MSSQL, problems, SQL Server 2014, SQL Server 2016, SQl server Administration

Improve SQL Server Performance with Compression

SQL Server data compression is now available in all editions of SQL Server, starting with 2016 SP1.

In this Article, you will not only know how data compression will save space, you’ll also find out how compression can sometimes improve performance as well.

Space Savings vs. Performance

When I first heard about compression back in 2008, my first thought is that it would have a big performance penalty. Compression would save disk space, but it would probably decrease performance as the data was compressed and decompressed. It turns out that compression can improve performance instead. Because compressed data fits in a smaller number of data pages, there are decreased I/O requirements. Since I/O is generally the bottleneck in SQL Server, this can improve performance. Compressed data also has a decreased memory requirement. When querying compressed data, a smaller number of pages will be copied to the buffer pool. The one area that is impacted is CPU. You do need to have some CPU headroom because compression will require some additional CPU resources.

The good thing is that if the workload is reasonably tuned, many SQL Server instances have more CPU resources than they need. One note of caution here. Don’t look to compression as the solution to solving major performance issues. You need to look at physical resources, configuration, indexing, and query tuning. The point I’m trying to make is that decreased I/O and better memory utilization will benefit the workload in many cases.

Continue reading “Improve SQL Server Performance with Compression” →

May 8, 2013 Mustafa EL-Masry Configuration, Performance MSSQL

Disable or Enable Trace File by T-SQL

T0 Disable or Enable Trace File 0 = Disable , 1 = Enable

EXEC master.dbo.sp_configure ‘allow updates’, 1;
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 0; *********************** Change here
GO
EXEC master.dbo.sp_configure ‘default trace enabled’, 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 1;
GO
EXEC master.dbo.sp_configure ‘allow updates’, 0;
GO

February 19, 2012June 15, 2013 Mustafa EL-Masry Configuration, Configuration

Dedicated Administrator Connection [DAC] in SQL Server

Introduction

DAC [Dedicated Administrator Connection ] is feature added from SQL Server 2005 version . Database administrator use this fetaure to connect to a SQL Server instance When the database engine not responding to the regular connection so the database administrator use this fetaure to connect to the instance to solve the problem Instead of rebooting the SQL Server.

By Defult this feature is disable in All SQL Server version From 2005 to the New version 2012.

Note : your SQL Server browser must be Runing

enable the DAC using below T-SQL command

Use master

sp_configure ‘show advanced options’ , 1

sp_configure ‘remote admin connections’, 1

G O RECONFIGURE

enable the DAC using SSMS

1- write click on your SQL Server Probirties >> Factes >> Service Area Configuration >> RemoteDacEnabled

Change it from False To True

Now try to connect by write ADMN: before your instance name like this

ADMIN:Mostafa-PC\SQLSERVER2008

then your User name and your password or connect windows Authentication but if you try to connect by the default way SQL Server will give you this Error

Dedicated administrator connections are not supported. (ObjectExplorer)