Installing SQL Server 2016

Configuring PolyBase: Configuring Service Accounts: Configuring TempDB options: Configuring access to SQL Server instance   Configuring SQL Server Analysis Services   Configuring SQL Server Reporting Services   Aggrement on R License Completing the installation: If SQL Server Management Studio did not load, allocate it in MS website, download it, and install it separately:

SQL Cursors

I see SQL cursor as a loop on the table. It is highly inefficient, and can result in infinite loops if not used properly. I highly recommend trying other alternatives (like Common table Expressions – CTE’s) if it is possible. Anyway, here is the syntax of a SQL cursor SELECT * INTO tmpTbl FROM (SELECT 1 AS SaleID) A[…]

Management Data Warehouse

Management Data Warehouse is a very powerful tool for monitoring the SQL Server (CPU, Memory, Disk) on the server, database, and query level. Configure the Management Data warehouse: In the Monitoring server:Right click on Data Collection > Tasks > Configure Management Data Warehouse: Then walk on the wizard, and choose the security configuration, the user[…]

SQL Performance Optimization

Here we list the techniques for optimizing the query performance: Query Execution Plans Partitioning Partitioned Tables Partitioned Indexes  Partitioned Heap Indexes: Covering Indexes Filtered Indexes Indexed Views Report/DW Query Optimization De-normalization Routine Aggregation Using Summary Tables Performance Monitoring Monitoring Tools Key Query Performance Metrics Management Data Warehouse and Data Collection Components Performance Tuning Query Tuning[…]

SQL Server Disk Size too large?

If your SQL server disk size got too big, narrow down to the DBs: WITH fs AS (SELECT database_id,  type, size * 8.0 / 1024 size FROM   sys.master_files) SELECT recovery_model_desc, NAME, (SELECT Sum(size)   FROM   fs  WHERE  type = 0 AND fs.database_id = db.database_id) DataFileSizeMB, (SELECT Sum(size)   FROM   fs  WHERE  type = 1 AND fs.database_id = db.database_id) LogFileSizeMB FROM   sys.databases db ORDER  BY 3 DESC, 4 DESC If Log file is too large, check the recovery model. If the recovery model is mistakenly set to Full, then reset it Simple. If it is set to[…]

Query Performance

Querying: Execution Plans De-normalization Partitioning: partitioned column and queries directly & indirectly based on it. Partitioned indexes & partitioned heaps. Create Partition Function C1(date) As Range Right For Values (‘2014-01-01’, ‘2014-02-01’…) SELECT * FROM SYS.PARTITIONS WHERE OBJECT_NAME(OBJECT_ID)=’t1) ORDER BY INDEX_ID, Partition_ ID Covering indexes Filtered indexes: create index .. Where c1 > 1000 Indexes views[…]

SQL Server DW Security

Normally we grant read access to DW for all users. Here is a typical script you run to allow a user to conusme the DW: GRANT SELECT ON SCHEMA::[dbo] TO [user1] GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [user1] EXEC sp_addrolemember N’db_datareader’, N’user1′ Then to test it: EXEC(‘SELECT TOP 10 * FROM table1’) AS USER=‘user1’