System Center query performance or general slowness – Database Optimizations
Sometimes we get wrapped up in day to day operations and don’t notice that an application such as System Center can quickly come to it’s knees. All of a sudden you are left with an application
that is cumbersome or hard to use. The slowness could be for various reasons, but a good start is always the Hardware and Database layers.
Recently I came across a poorly performing application and noticed that it would run slowly from a workstation or from the database server. I ran the first query below to check for fragmentation
of the database, then ran the a reindex followed up by an update statistics.
Important: Take utmost care with these queries and ensure to monitor performance of the DB Server\Instance while running. I typically shut down all Services\Connections to the Database Server
while the optimizations take place.
1. Open SQL Server Management Studio, run the query below to check fragmentation:
SELECT a.index_id, name, avg_fragmentation_in_percent FROM
sys.dm_db_index_physical_stats (NULL,NULL, NULL, NULL, NULL) AS a JOIN sys.indexes
AS b ON a.object_id = b.object_id AND a.index_id = b.index_id Where
avg_fragmentation_in_percent > 30
2. Then run this command on the tables (may take a while):
EXEC sp_MSForEachtable ‘DBCC DBREINDEX (”?”)’
4. Then, run this query (may take a while)
EXEC sp_MSForEachTable “UPDATE STATISTICS ? WITH FULLSCAN”
MS SQL Server R Services
So, here we go. I think we have a very interesting fly of though here. First of all, the first question, Still i am trying to undestand how R benefits SQL Server is a bit of a strange question. R does not have to benefit SQL Server. R is a standalone language with all of its perks and downside’s and it seems to work quite well in its own niche.
R Services (In-Database) provides a platform for developing and deploying intelligent applications that uncover new insights. You can use the rich and powerful R language and the many packages from the community to create models and generate predictions using your SQL Server data. Because R Services (In-Database) integrates the R language with SQL Server, you can keep analytics close to the data and eliminate the costs and security risks associated with data movement.
R Services (In-Database) supports the open source R language with a comprehensive set of SQL Server tools and technologies that offer superior performance, security, reliability and manageability. You can deploy R solutions using convenient, familiar tools, and your production applications can call the R runtime and retrieve predictions and visuals using Transact-SQL. You also get the ScaleR libraries to improve the scale and performance of your R solutions.
SQL Server setup, you can install both server and client components.
R Services (In-Database): Install this feature during SQL Server setup to enable secure execution of R scripts on the SQL Server computer.
R scripts, and a new service is created, the SQL Server Trusted Launchpad, to manage communications between the R runtime and the SQL Server instance.
Microsoft R Server (Standalone): A distribution of open source R combined.Both R Services (In-Database) and Microsoft R Server (Standalone) include the base R runtime and packages, plus the ScaleR libraries for enhanced connectivity and performance.
Microsoft R Client is available as a separate, free installer. You can use Microsoft R Client to develop solutions that can be deployed to R Services running on SQL Server, or to Microsoft R Server running on Windows, Teradata, or Hadoop.
SQL Server, on the other hand, is a fairly good RDBMS system, which has everything a user might need from ACID to supporting the storage of fairly large data volumes. What SQL Server lacks, and in my opinion will not gain anytime soon, is a sophisticated way to crunch data for analysis and a way to do machine learning, not even to start speaking of deep learning. It is just not designed for this. As a matter of fact, many RDBMS systems are just not designed for this.
The other problem is that SQL Server costs. A lot, if you don’t know what you are doing or if you know what you are doing but have a lot of data and big intentions.
And now we get to the second discussion – the one on the analytical part of the RDBMS. It does not have to be hard to do, it is just matter of some strategy. Think about it this way – how often does it happen for a user to be needing to analyze ALL of their data? Not really so often. And the actual RDBMS system should not be scaled for this either, since most of them either can’t scale up, or cost a lot to do so. (Yep, some of the vendors make big money out of this – think about SQL Server, for example – functional columnstores in PDW, and just silly remains of functionality in SQL Server)
So, here is an idea: keep your data in RDBMS, if you need to. Fine, RDBMS will guarantee you transactions, recovery, speed and storage. For the analytical part do this: get an appliance server – it is like a toaster, it does one thing but it does it well – and make sure you load plenty of memory on it. Memory is cheap.
Then do some data cleaning and pre-aggregations in your RDBMS – after all you are paying crazy amounts of money for it, so might as well make it work for it
then load the prepared data in the other cheap server, in memory with R and do the real heavy lifting there. If needed, return some results to be written in your RDBMS for later use.
The idea is fairly simple – just give the data owners the option to get a fairly cheap way to crunch the data and they will be happy. Sure, you can count on it, but knowing Microsoft, it will take a long while before it’s useful, and with other providers you still have to do your own heavy lifting. Might as well do the heavy lifting here and now, and get some competitive advantage here and how (instead of waiting for it to come to you later).
Important: Microsoft R Server (Standalone) is a separate option designed for using the ScaleR libraries on a Windows computer that is not running SQL Server.
However, if you have Enterprise Edition, we recommend that you install Microsoft R Server (Standalone) on a laptop or other computer used for R development, to create R solutions that can easily be deployed to an instance of SQL Server that is running R Services (In-Database).
Here is some useful link:
What is new in SQL Server Machine Learning Services?
SQL Server R Services
Let me know if you have any further question and your comments will be learning point.
— Microsoft Certified Solutions Associate (MCSA)
What are the steps involved in a simple upgrade to SQL Server 2014? I am currently running an older version of SQL Server and want to do an in place upgrade.
In this tip, we will demonstrate an upgrade to SQL Server 2014.
Note: Before starting make sure you are connected to a network and have Internet access.
After you launch the SQL Server setup, the SQL Server Installation Center window will display. Along the left side of the window are the categories showing how the SQL Server Installation Center is organized. On the right side of the window are different actions the installer can take. Notice that some of the items allow the installer to view documentation while other items will make changes to the system. By default the Planning category is displayed.
Clicking on Installation on the left side will display the different installation options. For this tip, we will click on “Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012”. This will launch the Upgrade to SQL Server 2014 application.
The following pop-up window might appear several times throughout the installation process.
The Upgrade to SQL Server 2014 application lists the steps it will follow on the left side of the window. Enter the product key and click “Next >”.
Review the license terms, click on “I accept the license terms”, and then click “Next >”.
The upgrade application will download, extract and install files needed to carry out the installation process. Click on “Next >” to continue.
Select the SQL Server instance to modify. Click on “Next >” to continue.
Sometimes a “Please Wait…” pop-up box might appear.
The next step is the feature selection. In the example shown below, all of the features will be upgraded. Click on “Next >” to continue.
The Instance Configuration screen allows the installer to specify the instance ID. This screen will also display other installed instances. In this example, the MSSQLSERVER instance on SQL Server 2012 will be upgraded to SQL Server 2014.
The Server Configuration screen shows the services to be installed, the service account name and the Startup Type. If you are installing SQL Server 2014 on a device with limited resources then the Startup Type should be set to Automatic (Delayed Start) instead of Automatic. Click on “Next >”.
The Full-text Upgrade screen provides three Full-Text upgrade options (import, rebuild and reset).
The Feature Rules screen will execute a rule check to make sure the upgrade process should successfully.
The Ready to Upgrade screen displays all of the features and prerequisites to be installed or upgraded. At this point, the installer can still go back to make changes or quit the process. Click on “Upgrade” to begin the upgrade of the SQL Server 2014 components.
The upgrade progress bar tracks the status of the upgrade.
Upon completion of the installation, click on “Close” to exit the Upgrade to SQL Server 2014 application.
The SQL Server Installation Center can now be closed.
MS SQL database backup file
SQL Server 2016 Community Technology Preview 2 (CTP2) through current version).
To make your database secured and protective; it helps you to restore your inaccessible files of the main database when any type of corruption or damage occurs. But, what if backup also corrupt, while attempting to restore database from backup file. There could be multiple reasons behind such disaster situation, here in this write-up you will get to know about the reason and solutions behind such case, where SQL Server user faces corruption.
— SQL Server database backup and not able to restore their databases.
SQL backup files are basically a replica of your original SQL database, which can be located in different locations on the system. There could be multiple reasons of inaccessible backup file. Here are some most common causes of damaged SQL BAK files:
— Virus attack
— Abrupt system shutdown
— Use of a wrong driver
— Bad sectors in your system’s hard disk
— Sudden removal of a selected tables, records, and procedures
— unconventional functioning of Hard disk
— Improper shutdown of application
— Wrong database synchronization
— System crash
— corrupt database system rules and tables
The most common error message during restoration of database is: ‘Backup or restore operation terminating abnormally.’ A Backup restoration error occurs when a filemark in the backup device could not be read. There could be multiple causes of when a user encounters a filemark error. The most common reasons are:
— A media failure may arise on the same device where the backup is stored
— A write failure may occur while creating the backup file
— Loss of connectivity may arise while creating a network backup
— A failure in the Input/Output path occurs in the disk just after successful write to the disk
After backup restore error the first thing you could do is to check whether all the sets of backup have issues or just some sets have issues. It might be possible that only some sets of backup have issues due to which you are getting restore error. In order to retrieve other backup sets from the device, you need to specify the file number. In case, there are multiple backup sets available on a single device, then to determine the usable backup, you can run the following query:
RESTORE HEADERONLY FROM DISK='<Backup Location>’
If you got the usable set from the disk, copy it to another drive for usage and try to restore the damaged files with the help of SQL restore commands. Here are some of the SQL commands that you can use to restore corruption in your SQL database backup, the following essential backup concepts:
To recover a database use the following command. This will put your database in the “restoring” state
RESTORE DATABASE <DB Name> FROM DISK='<Backup Location>’ WITH FILE = <FileNumber>
— Write the backup set number instead of ‘FileNumber’ that you want to restore.
The following command will take the database, which is in ‘restoring’ state and make it available for end users.
RESTORE LOG <DB Name> FROM DISK = ‘<Backup Location>’
“Before you can create the first log backup, you must create a full backup ”
The above mentioned commands are used to restore corrupt backup file of SQL database. However, these corrupt backup recovery solutions provided by Microsoft are not applicable for deep corruption cases. In order to restore your highly damaged or corrupt SQL backup database you can always choose a third party SQL backup recovery software. These professional utilities are designed to restore data from a corrupt (.BAK) SQL backup file.
BACKUP DATABASE AdventureWorks2012
MEDIANAME = ‘AdventureWorksStripedSet0’,
MEDIADESCRIPTION = ‘Striped media set for AdventureWorks2012 database;
Third party applications have functions to restore SQL backup file due to all above mentioned reasons. Before buying any professional backup recovery tool, you need to choose the most reliable one. For that you should use the online demo versions of the backup recovery applications to test their efficiency.
For more information and examples, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. For a tutorial, see Tutorial: SQL Server Backup and Restore to Windows Azure Blob Storage Service.
Security: Beginning with SQL Server 2012, the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. It is still possible to restore backups created with passwords.
Permissions: BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
Ownership and permission problems on the backup device’s physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Such problems on the backup device’s physical file may not appear until the physical resource is accessed when the backup or restore is attempted.
Let me know if you have any further question and your comments will be learning point.
— Microsoft Certified Solutions Associate (MCSA)
MS SQL Server 2014 HotfiX’s use syntax i.e. Select @@version
SQL Database Engine Instances – 2014
Topic: Task Description
Describes how to configure the properties of an instance. Configure defaults such as file locations and date formats, or how the instance uses operating system resources, such as memory or threads.
Describes how to manage the collation for an instance of the Database Engine. Collations define the bit patterns used to represent characters, and associated behaviors such as sorting, and case or accent sensitivity in comparison operations.
Describes how to configure linked server definitions, which allow Transact-SQL statements run in an instance to work with data stored in separate OLE DB data sources.
Describes how to create a logon trigger, which specifies actions to be taken after a logon attempt has been validated, but before it starts working with resources in the instance. Logon triggers support actions such as logging connection activity, or restricting logins based on logic in addition to the credential authentication performed by Windows and SQL Server.
Describes how to manage the service associated with an instance of the Database Engine. This includes actions such as starting and stopping the service, or configuring startup options.
Describes how to perform server network configuration tasks such as enabling protocols, modifying the port or pipe used by a protocol, configuring encryption, configuring the SQL Server Browser service, exposing or hiding the SQL Server Database Engine on the network, and registering the Server Principal Name.
Describes how to perform client network configuration tasks such as configuring client protocols and creating or deleting a Server Alias.
Describes the SQL Server Management Studio editors that can be used to design, debug, and run scripts such as Transact-SQL scripts. Also describes how to code Windows PowerShell scripts to work with SQL Server components.
Describes how to use maintenance plans to specify a workflow of common administration tasks for an instance. Workflows include tasks such as backing up databases and updating statistics to improve performance.
Describes how to use the resource governor to manage resource consumption and workloads by specifying limits to the amount of CPU and memory that application requests can use.
Describes how database applications can use database mail to send e-mail messages from the Database Engine.
Describes how to use extended events to capture performance data can be used to build performance baselines or to diagnose performance problems. Extended events are a light-weight, highly scalable system for gathering performance data.
Describes how to use SQL Trace to build a customized system for capturing and recording events in the Database Engine.
Describes how to use SQL Server Profiler to capture traces of application requests coming in to an instance of the Database Engine. These traces can later be replayed for activities such as performance testing or problem diagnosis.
Describes the Change Data Capture (CDC) and Change Tracking features and describes how to use these features to track changes to data in a database.
Describes how to use the Log File viewer to find and view SQL Server errors and messages in various logs, such as the SQL Server job history, the SQL Server logs, and Windows event logs.
Describes how to use the Database Engine Tuning Advisor to analyze databases and make recommendations for addressing potential performance problems.
Describes how the production database administrators can make a diagnostic connection to instances when standard connections are not being accepted.
Describes how to use the deprecated remote servers feature to enable access from one instance of the Database Engine to another. The preferred mechanism for this functionality is a linked server.
Describes the capabilities of Service Broker for messaging and queueing applications and provides pointers to the Service Broker documentation.
Describes how the buffer pool extension can be used to provide seamless integration of nonvolatile random access storage (solid-state drives) to the Database Engine buffer pool to significantly improve I/O throughput.
String Functions (T-SQL)
Metadata Functions (T-SQL)
XQuery is a language that can query structured or semi-structured XML data. With the xml data type support provided in the Database Engine, documents can be stored in a database and then queried by using XQuery. That can help in understanding the implementation of XQuery in the Database Engine.
|XML Data (SQL Server)||Explains the support for the xmldata type in the Database Engine and the methods you can use against this data type. The xml data type forms the input XQuery data model on which the XQuery expressions are executed.|
|XML Schema Collections (SQL Server)||Describes how the XML instances stored in a database can be typed. This means you can associate an XML schema collection with the xml type column. All the instances stored in the column are validated and typed against the schema in the collection and provide the type information for XQuery.|
|XQuery Basics||Provides a basic overview of XQuery concepts, and also the expression evaluation (static and dynamic context), atomization, effective Boolean value, XQuery type system, sequence type matching, and error handling.|
|XQuery Expressions||Describes XQuery primary expressions, path expressions, sequence expressions, arithmetic comparison and logical expressions, XQuery construction, FLWOR expression, conditional and quantified expressions, and various expressions on sequence types.|
|Modules and Prologs (XQuery)||Describes XQuery prolog.|
|XQuery Functions against the xml Data Type||Describes a list of the XQuery functions that are supported.|
|XQuery Operators Against the xml Data Type||Describes XQuery operators that are supported.|
|Additional Sample XQueries Against the xml Data Type||Provides additional XQuery samples.|
More info here W3C specification is available here
Encrypted Backup in SQL Server 2014
Encryption for Backups is a new feature introduced in SQL Server 2014 and the benefits of this option are
1.Encrypting the database backups helps secure the data.
2.Encryption can also be used for databases that are encrypted using TDE.
3.Encryption is supported for backups done by SQL Server Managed Backup to Windows Azure, which provides additional security for off-site backups.
4.This feature supports multiple encryption algorithms including AES 128, AES 192, AES 256, and Triple DES
5.You can integrate encryption keys with Extended Key Management (EKM) providers.
The following are pre-requisites for encrypting a backup:
1.Create a Database Master Key for the master database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomePass@WordStronG123′;
2. Create a certificate or asymmetric Key to use for backup encryption.
CREATE CERTIFICATE CertforBackupEncryption
WITH SUBJECT = ‘Certificate for Backup Encryption ‘;
3. Backup the database with encryption:
BACKUP DATABASE [addarr]
TO DISK = N ‘D:Backup ’
ALGORITHM = AES_256,
SERVER CERTIFICATE = CertforBackupEncryption
STATS = 10
Restoring the encrypted backup:
SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key. If you are restoring the encrypted backup to a different instance, you must make sure that the certificate is available on that instance. More info
Microsoft SQL Server 2014 White Papers
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014
- Database Backup Encryption
- Idle Connection Resiliency
- In-Memory OLTP – Common Workload Patterns and Migration Considerations
- In-Memory OLTP – SQL Server In-Memory OLTP Internals Overview
- Introducing Microsoft Business Intelligence (BI) Tools
- Microsoft IT: A Case Study on “Hekaton” against RPM – SQL Server 2014 CTP1
- Migrating Content Between Report Servers
- Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
- SQL Server 2014 and Windows Azure Blob Storage Service: Better Together
- Using Power BI in a Hybrid Environment
- Books Online for SQL Server 2014
- Developer Reference for SQL Server 2014
- Installation for SQL Server 2014
- Setup and Servicing Installation
- Upgrade Advisor
- Tutorials for SQL Server 2014
- Microsoft JDBC Driver 4.0 for SQL Server
- Microsoft Drivers for PHP for SQL Server
- Microsoft ODBC Driver for SQL Server
- Microsoft OLE DB Provider for DB2 Version 5.0