SQL Server - MSSQL - Environment

Here is the concept to create MSSQL - Environment with Expiry

SQL Server - CHW

 'CHW' I mean to say Cumulative Updates and Hotfix detail with Window Update Links

SQL Server - Query to find Duplicate Records in a table

Query to find duplicate records

SQL Server - HotFixes (Logshipping)

HotFixes  - Logshipping

SQL Server Events and Notification

- SQL Server Database Mail

- SQL Server Mail Operator

- SQL Server Push Notification

https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/working-with-query-notifications?view=sql-server-ver16

- SQL Server Events

Additionally,

SQL Server MVP

Related Article

https://mvp.microsoft.com/en-us/overview

List of Microsoft Cloud Partner.

Related Article/s

https://partner.microsoft.com/en-US/

https://learn.microsoft.com/en-us/partner-center/find-a-partner

Best Regards

Amit Vaid

SQL Server - Performance DashBoard

Performance DashBoard

SQL Server - Bulk Insert Best Practices & Books Online

Bulk Insert Best Practices & Books Online

SQL Server - Enable Trace Profiler (Query Analyser)

Using SSMS Query Analyser

SQL Server - Tech Antons

Tech Antons

Antons is derived from the word 'Antonym' which is another way of normal practice from SQL Server Technical Best Practices.

It is suggested to run below maintenance activities on daily basis which should take around 15-30 mnts.

SQL Server - Disaster Recovery and High Availability Updates

Disaster Recovery and High Availability Updates

Related Article..

https://support.microsoft.com/en-au/topic/kb5007182-cumulative-update-14-for-sql-server-2019-67b00a61-4f30-4a36-a5db-b506c47e563b

SQL Server - Collation Settings

In this article we will learn on how to change the collation settings at database level.

Use [Poland-Showroom-DB]

SELECT name, collation_name FROM sys.databases WHERE name = 'Poland-Showroom-DB';

SQL Syntax

-- make sure database in Single user mode

ALTER DATABASE [Poland-Showroom-DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- change collation to Polish_CI_AS

ALTER DATABASE [Poland-Showroom-DB] COLLATE Polish_CI_AS;

-- allow database in operational mode

ALTER DATABASE [Poland-Showroom-DB] SET MULTI_USER

Here is the related article to cross verify the Windows and SQL Language Code Identifiers (LCID).

https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16

Best Regards

Amit Vaid

SQL Server - Browser Service 'Disabled'

Browser Service is 'disabled'

IMPACT
If the SQL Server Browser service isn't running, the following connections do not work:

Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).
Any component that generates or passes server\instance information that could later be used by other components to reconnect.
Connecting to a named instance without providing the port number or pipe.
DAC to a named instance or the default instance if not using TCP/IP port 1433.
The OLAP redirector service.
Enumerating servers in SQL Server Management Studio or Azure Data Studio.

How to enable Browser Service


Related Article

https://learn.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service?view=sql-server-ver16

SQL Server - Ultimate 'Group By' Clause

 SQL Server Group By Clause

SQL Server - TimeStamp AutoUpdate

Trigger for Insert and Update

SQL Server Architecture with ACID Properties

SQL Server Architecture with ACID Properties

Related Article

https://amit-sqldbasupport.blogspot.com/2022/11/sql-server-architecture.html

Acid Properties

https://learn.microsoft.com/en-us/windows/win32/cossdk/acid-properties

Common Fatal Errors

Related Article

https://social.technet.microsoft.com/wiki/contents/articles/53891.sql-server-troubleshooting-common-db-corruption-causes.aspx

https://learn.microsoft.com/en-us/dynamics-nav/troubleshooting--a-fatal-error-occurred.-the-connection-to-sql-server-cannot-be-established

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-ver16

https://support.microsoft.com/en-us/topic/kb3173976-fix-fatal-error-when-you-run-a-query-against-the-sys-sysindexes-view-in-sql-server-2016-a4803f29-0c24-6813-82c5-8a540b96011b

Best Regards

Amit Vaid

SQL Server - Top Recommendations

 SQL Server - Top Recommendations

1. TempDB - The Number of Logical CPUs, the number of Tempdb Data files it should be.

2. Target Memory counter should not exceed maximum allocated memory. Anyhow we should continuously monitor its spike before allocating more memory.

3. Page Life Expectancy value should be more than 300 as good sign.

4. Buffer Cache Hit Ratio - More than 90%

5. Continuously capture/watch the number of active virtual log files for the database to be able to analyze the overall need for Log file initial size.

6. DBCC Checkdb should run for all the database fortnightly/weekly.

7. Verify Backups - It's necessary that Backup set should be valid once finished (but takes some more time).

For rest other missing recommendations, we will continuously work on this article and provide some more info to improve the performance and proactive monitoring of your databases.

Best Regards

Amit Vaid

SQL Server - Substring Function (Very Useful)

 Use of SUBSTRING function....

Related Article

https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver16

SQL Server - Monitoring Magnets

SQL Server - Monitoring Magnets

SQL Server - Blocking and Huge Tempdb

Blocking and Huge Tempdb

https://learn.microsoft.com/en-us/troubleshoot/sql/performance/understand-resolve-blocking

https://support.microsoft.com/en-us/topic/how-to-shrink-the-tempdb-database-in-sql-server-ea0a95c2-eff8-7075-9ee2-2ee42226ca1c

SQL AWAITING COMMAND

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/26f36dd3-e5c0-452f-8513-d8c78c3e3e09/sql-awaiting-command-sleeping-bloking-most-of-the-running-processes-wthout-genrating-any-deadlock?forum=sqldatabaseengine

SQL Server Important Tools Links

https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16

Additionally

Example of GroupBy Clouse

USE [NewAmondsShop]

SQL Server - Shared Memory Protocol

Connecting from Another Computer

By using the Shared memory protocol, Management Studio will connect to the Database Engine.

https://learn.microsoft.com/en-us/sql/relational-databases/lesson-2-connecting-from-another-computer?view=sql-server-ver16

Additionally,

Please also read about Digital Signature.

https://learn.microsoft.com/en-us/sql/t-sql/statements/add-signature-transact-sql?view=sql-server-ver16

Additionally,

Please also learn below concept for security reasons.

We may have to change SQL Server default port from 1433 to any other unknown number. 

Best Regards

Amit Vaid

SQL Server - 'JAR'

HANDI (T-SQL) 'JAR'

- Joins

- Attributes

- ROW_Number

Related Articles...

https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/master-data-services/attributes-master-data-services?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16

Best Regards

Amit Vaid

SQL Server - Add Primary Key to Existing Table

Q. How to add Primary Key to an Existing Table?

Ans...

The Simple steps to add Primary Key / Clustered Index over existing table.

USE [DairyShop]

CREATE TABLE MILK_Transactions

(ID int Identity(1,1),

Customer_ID nvarchar(50),

CustomerName varchar(100),

SoldQty_in_Ltr tinyint,

Per_Ltr_PriceRs decimal(10,2),

AmountRs decimal(10,2),

Payment_Mode char(25)

)

ALTER Table MILK_Transactions add primary key (ID)

Best Regards

Amit Vaid

SQL Server - Monitoring Mostips

Monitoring Mostips

SQL Server - Backward Compatibility & Builds

Backward Compatibility Related Links

https://learn.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility

https://learn.microsoft.com/en-us/answers/questions/153022/download-sql-server-2005-backward-compatibility-fi.html

https://learn.microsoft.com/en-us/sql/relational-databases/replication/replication-backward-compatibility?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16

https://azure.microsoft.com/en-in/updates/ssdt-support-for-1500-compatibility-level/

Build Versions

https://support.microsoft.com/en-us/topic/kb4518398-sql-server-2019-build-versions-782ed548-1cd8-b5c3-a566-8b4f9e20293a

https://support.microsoft.com/en-us/topic/kb4047329-sql-server-2017-build-versions-346e8fcd-c07c-5eeb-e10b-e3411ba8d8dd

Additionally,

Recent versions of SQL Server allows you to directly upgrade from SQL Server 2008 / R2 and keep the database compatibility to the earlier version

Best Regards

Amit Vaid

SQL DBA Support 'Sampark' or Contact

Email:- amitvaid81@gmail.com

Here we can ask either for Standard GUide with 'WAH'

W:- Wait Type Analysis / Windows Event Logs (Some Common Errors)

A:-  Architecture (Basic)

H:-  Hints, Tips and Tricks

or / along with we can also ask to send 'AT-IQ' which is A Test book with General Interview Questions.

Best Regards

Amit Vaid

(SPs) - SPROCs related to Performance.

SPS- System's Performance Stored Procedures

SQL Server 'BHU'

'BHU'

Basic of 'High Availability Feature' with 'Unique Availability Group Name'

SQL Server 'V' and 'E'

There are some important concerns / correlation between these two characters.

1. 'VLF' and Execution related DMVs.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-info-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/execution-related-dynamic-management-views-and-functions-transact-sql?view=sql-server-ver16

2. Versions and Editions

https://social.technet.microsoft.com/wiki/contents/articles/783.sql-server-versions.aspx

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver16

3. Volume Maintenance Task and Enable Instant file initialisation.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver16

Additionally,

Basic Help on Architecture (BHA) - Rationals are requested to collect only the important parts from given articles as there may be some other lengthy paragraphs that can be ignored.

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-architecture?view=sql-server-ver16

https://amit-sqldbasupport.blogspot.com/2022/11/sql-server-architecture.html

Best Regards

Amit Vaid

SQL Server's Maintenance 'SHAUP'

Maintenance 'SHAUP'

There are certain activities that are very useful in day to day maintenance.

S:- Setting Up Log File for Maintenance Jobs to find the route cause in case of failures.

H:- History Cleanup - We can automate History cleanup for text/Backup files and Logs in msdb database.

A:- Apply the latest Service Pack and C-Updates.

U:- Update Statistics and Index Rebuild/Re-Org

P:- Perfmon and Profiler to capture Performance Bottlenecks in one of the Table in DBA database (Note:- Please ensure to run profiler in client system to avoid system hang/temp file space Full).

To run the maintenance either during off hours or for SOMETIME there is maintenance window approved by application owner and Your Goodself can schedule the jobs accordingly.

THERE are certain things that can be found from SQL Server such as Deadlocks, Logins and Job failure etc.

It's necessary to find ONGOING activities from DMVs and sys.sysprocesses such as Long running/High CPU queries and Blocking.

When we say 'SIT' in regards to SQL Server, its nothing but 'SQL Internal Tutorial'

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/sql-server-in-memory-oltp-internals-for-sql-server-2016?view=sql-server-ver16

Additionally,

1. Two things that can be checked thoroughly while facing performance issue.

- SQL Server Error Logs
- Windows Event Logs

2.

True

- The permissions for DDL and DML activities can be given / managed separately.

False

- Truncate cannot be rolled back.

3.

Please also read

Data Transfer Techniques

- Replication
- Log Shipping
- Linked Server
- Import Export

Related Links...

https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/transferring-data?view=sql-server-ver16

https://learn.microsoft.com/en-us/troubleshoot/sql/security/transfer-logins-passwords-between-instances

Best Regards

Amit Vaid

SQL Server Temp table and temp Variable

SQL Server Temp table and temp Variable

https://learn.microsoft.com/en-us/answers/questions/619200/temporary-table-vs-table-veriable.html

REname columns (Database Engine)

https://learn.microsoft.com/en-us/sql/relational-databases/tables/rename-columns-database-engine?view=sql-server-ver16


SQL Server Charge

https://www.microsoft.com/en-in/sql-server/sql-server-2019-pricing

Additionally,

Please also read Certification's Related article

https://learn.microsoft.com/en-us/certifications/mcsa-sql-certification/

https://trainingsupport.microsoft.com/en-us/mcp/forum/all/which-certification-to-go-for-as-a-sql-dba-in-2021/e2275332-8175-4e35-8b89-a0ef0ed7e501

https://trainingsupport.microsoft.com/en-us/mcp/forum/all/latest-certifications-for-sql-server-dbas/b431eaac-bd88-429c-933e-eae90d9ad92f

SQL Server 'KRA'

SQL Server 'KRA'  and 'Anti' Virus Exclusions....

- Knowledge of RDBMS Architecture
- + Antivirus Exclusions

Related Articles.....

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-guides?view=sql-server-ver16

https://learn.microsoft.com/en-us/troubleshoot/system-center/scom/antivirus-exclusions-recommendations

Best Regards

Amit Vaid

Change JSON Data with Built-in Functions, Validate and Query

 Change JSON Data with Built-in Functions (SQL Server), Validate and Query

https://learn.microsoft.com/en-us/sql/relational-databases/json/validate-query-and-change-json-data-with-built-in-functions-sql-server?view=sql-server-ver16

RAISERROR (Transact-SQL)

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver16

How to license SQL Server 2019 Standard Edition

https://www.microsoft.com/en-in/sql-server/sql-server-2019-pricing/

Introducing data virtualization with PolyBase

SQL Server Target Memory If running Slow....

https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-memory-usage?view=sql-server-ver16

https://learn.microsoft.com/en-us/troubleshoot/sql/performance/troubleshoot-slow-running-queries

Introducing data virtualization with PolyBase

https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver16

SQL Server SEE license / Purchase Model.

SQL Server SEE license / Purchase Model.

Here is the query to find the Detail.

SELECT create_date AS 'SQL Server Installed Date',
Expiry_date AS 'SQL Server Expiry Date',
DATEDIFF(dd,create_date, GETDATE()) 'No_of_Deys_Used',
ABS(DATEDIFF(dd,expiry_date,GETDATE())) 'No_Of_Daiys_Left'
FROM
(SELECT sp.create_date, DATEADD(dd, 180, sp.create_date) AS Expiry_date
FROM sys.server_principals sp
WHERE sp.name = 'NT AUTHORITY\SYSTEM') as exp_date_tb

&

Select @@VERSION

Additionally,

The Memory Utilization can be observed from DBCC MEMUSAGE.

There are other techniques to find the utilisation Database Wise.

SELECT DB_NAME(database_id),
COUNT (1) * 8 / 1024 AS MBUsed
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
GO

SQL Server Data Types

What is the standard length of a Barcode?

A UPC barcode contains 12 digits.

However, most of the places/countries it's 13-digit EAN barcodes to identify products.

Bar Code Preferred DataType and Limit

So, CHAR(13) would probably be the most appropriate data type to store this data.

Related Article

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

Additionally,

Please also read about SQL Server 'ODA'

O - ODBC Connection String
D - Data Tools
A - Azure Blob Storage

Best Regards

Amit Vaid

SQL Server LATEST CU Upgrades

Link.................
https://learn.microsoft.com/en-us/sql/database-engine/install-windows/latest-updates-for-microsoft-sql-server

We can 'GO ahead' anytime during off hours with downtime approval and apply the same .

Additionally,

The 'ye' Options is not just two characters, it can also be considered as 'your enabled' Options where we can query and fetch the desired info quickly.

send email to amitvaid81@gmail.com to receive the queries.

Best Regards

Amit Vaid

SQL Server 'SALES'

Nowadays companies should start switching from any other database to Microsoft SQL Server because of it's easy to implement features and low cost.

And this 'S A L E S' article reminds us to keep few things noted while purchasing as mentioned below.... 

S:- Search for suitable license for your IT Infrastructure by Number and Model of your Servers with client machines.

Related Articles...

https://www.microsoft.com/en-us/licensing/product-licensing/client-access-license

https://download.microsoft.com/download/3/d/4/3d42bdc2-6725-4b29-b75a-a5b04179958b/percorelicensing_definitions_vlbrief.pdf

https://www.microsoft.com/en-in/d/sql-server-2019-standard-edition/dg7gmgf0fkx9?activetab=pivot:requirementstab#tab134d2ff1b-1082-4f68-a050-e887a89cba99

A:- Analyse the need for required features.

Related Articles

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16

L:- Limit
It's necessary to understand the maximum limits and match with your actual requirements.

Related Articles
https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16

E:- Editions

After understanding the features requirement, it's time to check the availability in different editions before raising the requirement.

Related Articles...

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16

S:- Six Sigma - Later, we can also plan to implement Six Sigma Techniques i.e.
DMAIC (Define—Measure—Analyze—Improve—Control) OVERALL this helps to optimise the processes and reduce the cost.

A Freelancer / Companies should feel comfortable by charging anything between 10-20K to help analysing overall SQL Server requirement before creating PO with Software License Provider / Partner.

Best Regards Always

Amit Vaid

SQL Server 'JADU'

SQL Server JADU --- 'Joins',  'Alters' ,  'DDL' & 'UNION'

J:- Joins

https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver16

A:- Alters

Alter Index

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver16

Alter Tables

https://learn.microsoft.com/en-us/sql/relational-databases/tables/add-columns-to-a-table-database-engine?view=sql-server-ver16

Alter Permissions

https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver16

D:- DDL (Data Definition Language)

https://learn.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver16

U:- Union

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-ver16

SQL Server - SQLCMD's Exit and Clearances

THEY have created a beautiful article for sqlcmd.
THEY have provided easy steps to follow.
THEY have made DBA's life easy for those who prefer to use DOS or those who comes from any other OS.

-----------------------------------------------------------------------------------------------

SQLCMD

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files through a variety of available modes:

[:] QUIT
Causes sqlcmd to exit.

[:] EXIT[ (statement) ]
Lets you use the result of a SELECT statement as the return value from sqlcmd. If numeric, the first column of the last result row is converted to a 4-byte integer (long). MS-DOS, Linux, and Mac pass the low byte to the parent process or operating system error level. Windows 200x passes the whole 4-byte integer. The syntax is:

:EXIT(query)

For example:

:EXIT(SELECT @@ROWCOUNT)

You can also include the EXIT parameter as part of a batch file. For example, at the command prompt, type:

sqlcmd -Q "EXIT(SELECT COUNT(*) FROM '%1')"

The sqlcmd utility sends everything between the parentheses () to the server. If a system stored procedure selects a set and returns a value, only the selection is returned. The EXIT**()** statement with nothing between the parentheses executes everything before it in the batch and then exits without a return value.

When an incorrect query is specified, sqlcmd will exit without a return value.

Here is a list of EXIT formats:

:EXIT
Does not execute the batch, and then quits immediately and returns no value.

:EXIT( )
Executes the batch, and then quits and returns no value.

:EXIT(query)
Executes the batch that includes the query, and then quits after it returns the results of the query.

If RAISERROR is used within a sqlcmd script and a state of 127 is raised, sqlcmd will quit and return the message ID back to the client. For example:

Related Article
https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver16


-----------------------------------------------CLEARANCE------------------------------------------------


In Addition to this, Please also read

SQL Server timely Clearance/s

1. Need to clear Waittype Stats timely to get fresh recent stats.

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)

2. Need to Cycle Error Logs timely.

EXEC sp_cycle_errorlog
EXEC msdb.dbo.sp_cycle_agent_errorlog

3. Though, BACKUP LOG <db_name> WITH truncate_only command, used for clearing the log file, is deprecated now.
We can still clear the log files by taking log backup
- DBCC sqlperf(logspace)
- Backup the logs and
- resize through DBCC shrinkfile

4. Clear TempDB

CREATE the necessary space by dropping objects in the filegroup, adding additional files to the filegroup,or setting autogrowth on for existing files in the filegroup.

5. To clear SQL Server's cache, run DBCC DROPCLEANBUFFERS, which clears all data from the cache.
Then run DBCC FREEPROCCACHE, which clears the stored procedure cache.
DBCC FREESYSTEMCACHE ('userdatabase')
DBCC FREESESSIONCACHE [ WITH NO_INFOMSGS ]

Beware, this is similar to restart of SQL services.


All the Best


Amit Vaid
amitvaid81@gmail.com

SQL Server 'Max' Options

Some of the Major SQL Server's 'Max' options/configurations.

- Max Worker Threads
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-ver16

- Max Server Memory Settings
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver16

- MaxDOP (Degree of Parallelism)
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16

- Max number of concurrent connections
https://learn.microsoft.com/en-us/answers/questions/1008380/maximum-number-of-concurrent-connections.html


Best Regards

Amit Vaid

SQL Server - Monitoring Key Metrics and Keyboard Shortcuts

( / )

Note:- There will be "Victory for Sure" if we just concentrate our work.

Related articles..

Surface Area Configuration

https://learn.microsoft.com/en-us/sql/relational-databases/security/surface-area-configuration?view=sql-server-ver16

Versions

https://learn.microsoft.com/en-us/troubleshoot/sql/releases/download-and-install-latest-updates

Keys and Constraints

https://learn.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints?view=sql-server-ver16

                                                                 ( \ )

Other Useful Links - SQL Server Monitoring Key Performance Indicators.

https://learn.microsoft.com/en-us/biz    /technical-guides/test-results-sql-server-key-performance-indicators

https://learn.microsoft.com/en-us/sql/ssms/sql-server-management-studio-keyboard-shortcuts?view=sql-server-ver16

In addition to this...Please also read

SQL Server Monitoring Key metrics

- Open Transaction using DBCC

- CPU Utilisation.

- High CPU Queries

- WaitType Analysis.

- Blocking/Deadlocks.

- DB Wise Fragmentation.

- Index Usage Stats

- Long Running Jobs/Jobs currently being executed.

- PerfMon / DMV Performance Counters.

- SQL and Agent Error Logs

In this category, you can search metrics for:

T-SQL queries
The buffer cache
Table-related resources
Locks
Resource pools
Indexes
Connections

Group By Clause

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16

Best Regards Always

Amit Vaid

SQL Server 'MADHAV'

 

SQL Server Architecture - Learning

Some of the SQL Server Architecture related topics.

 

P:- Physical and Logical Architecture.

 

E:- Engines (All Engines related to MSSQL RDBMS)

(While reading this you can also find understanding about SQL and Agent Error Logs)

N:- Native backup Types and Monitoring tools & Network Protocols.

(While reading this you can also find understanding about Begin Tran, Implicit/Explicit Transactions).

 

S:- System Databases

Regards

Amit Vaid

SQL Server 'GUAVA' Features

 This article, Coming soon..!

SQL Server Performance Peanuts....!

Just to share major components to find performance bottlenecks. Read this article for quick reference.

P - PerfMon, Major counter to look at.

Buffer Cache Hit Ratio.

Page Life Expectancy.

Memory Available vs Target.

Disk IO read/write.

CPU Utilization.

Total vs Target Memory etc.

E - Enabling Performance Features, MaxDOP, CPU and Memory Configurations.....

A - Analyse High CPU Queries/SPROCs and Analyse Execution plans for Missing Indexes.

N - NDF files/Secondary Files - We can plan to add another Filegroup and move large tables to new Filegroups as For example: DataFile2.ndf, DataFile3.ndf, and DataFile4.ndf, can be created on three disk drives and assigned to the filegroup "SecondaryFileGroup". A table can then be created specifically on this filegroup. Queries for data from the table will be spread across the three disks; it will improve performance.

U - Update Statistics, we can disable Auto Update Stats Setting and run the Update Stats During Off hours.

T - Transaction Logs (In this Category we can reduce the number of virtual Logs).

S - Simple Recovery Model (When we switch to Simple Recovery its minimally logged/Only Page allocations, hence improve performance.

However, there is no point-in-time recovery/No Log Backup can be taken as disadvantage.

Best Regards

Amit Vaid

Shifting Blog

Respected All,

This is to announce that we are planning to shift from our existing blog from WordPress to 

amit-sqldbasupport.Blogspot.com

We will keep you posted on the same.

Best Regards

Amit Vaid

amitvaid81@gmail.com

SQL Server Architecture

For Beginners

Two things to understand first in terms of understand SQL Server Architecture.....

A. Physical and Logical Architecture

B. Recovery Models

From Experts, their opinion and suggestion would be considered in regards to improve this article even more.

Thanks with Regards

Amit Vaid (Author)