In-Memory: DMVs related to In-Memory OLTP in sql server 2014
- Details
- Written by Ranjeet
I am writing this article to just outline all the newly created DMVs by Microsoft to help getting metadata about In-Memory OLTP objects. For DBAs it is very essential to know about how engine is performing and for that a proper monitoring is required. DMVs are very good light weight monitoring object which give us instantaneous statistics about insight of database. Below are the DMVs which help getting metadata about In-Memory object. I will explain about these DMVs in my subsequent articles.
- sys.dm_db_xtp_memory_consumers
- sys.dm_db_xtp_gc_cycle_stats
- sys.dm_db_xtp_hash_index_stats
- sys.dm_db_xtp_index_stats
- sys.dm_db_xtp_merge_requests
SQL Server: What are the new features introduced in sql server 2014
- Details
- Written by Ranjeet
In my last article, I discussed about new features of SQL Server 2012. Here in this article I will outline the new features and enhancement which has been introduced in 2014. Most of the new features is related to memory optimization, security and performance related. I will explain every features in details in my upcoming articles. Let have these features outlined below:
New Features related to database administration:
- Buffer pool extensions to SSD
- In-memory OLTP
- IN-memory DW
- Delayed Durability
- SQL Server datafile in window Azure
SQL Server: What are the new features introduced in sql server 2012
- Details
- Written by Ranjeet
It’s a very common question is being asked in interview that what are the new features have been introduced in new version of SQL Server 2012 or 2014. Sometimes we know all the points or even worked on those points but hard to remember as text book knowledge. Today I decided to summarize all those as text book and keep as an article. I will try to categorize those features while presenting. SQL Server 2012 has introduced a rich number of new features and made enhancement in existing features. These new features range from development to administration. Let have those as below:
New Features related to database administration:
- Always on features which is actually High availability and Disaster recovery solution
- Always on SQL Failover Clustering (FCI) across multi domain
- Always on Availability group
- Contained database
- Column store Index
POWERSHELL: How to count number of words in text file using PowerShell
- Details
- Written by Ranjeet
To count how many number of word a text contains. PowerShell provides capabilities to do in a single line command. But if we want to achieve same thing using other scripting language, we will have to go through number of line codes along with putting programing context too. What we need to do to get this. Let have an example:
Created a file name PSCheck.txt which contains below text:
“sqlserverbase.com is a blog site.
Its provides number of good articles on SQL Server.
That’s a great thing.”
Below is the code to get number of word count:
PS C:\Users\ranjeet> ($(foreach($EachLine in Get-Content D:\Ranjeet\PSCheck.txt
){$EachLine.Split(" ")})|sort).count
18
PS C:\Users\ranjeet>
POWERSHELL: Interview questions in windows PowerShell
- Details
- Written by Ranjeet
PowerShell is becoming first choice as scripting language. There are number reasons for this. It became an automation specialist scripting language. PowerShell is now a das heavily used to automate SQL server related task which includes installation, cluster setup, SQL clustering, mirroring setup, creating maintenance job, setting up high availability and number of more task. Here I am trying to list few possible interview questions on PowerShell. I will try to add SQL server related question too here.
- Please explain how PowerShell is object oriented scripting language?
- What is the benefit of being object oriented?
- Have you done any automation using PowerShell?
- What is the latest version of PowerShell?
- What are pre-requisites to install and work PowerShell?
- Please differentiate between Text processing Shell and Object Oriented processing Shell?
- What is Verb-Noun format in cmdlet command?
- Actually cmdlet command is .NET framework object which an instantiation of .NET framework class?
- What is the use pipeline operator (|)?
- Please explain use of Get-Member cmdlet?
POWERSHELL: How to display output per page wise in cmdlet shell in PowerShell
- Details
- Written by Ranjeet
As we all know PowerShell is task based object oriented command shell and scripting language program. Output of each command comes in the form of object not in text like traditional scripting language. So, to see the output on host screen, we need to convert output in text which can be presented on the host screen. PowerShell provide Out-Host command which pass the output of the any command out of the PowerShell. Since it take output out the PowerShell, we can paginate it as per our conveyance to see. There are two ways we can accomplish this task:
1. Get-Command | Out-Host -paging
2. Get-Command | more
Above two options are available to view output in paginated way.
POWERSHELL: One Vs Other interview questions in PowerShell
- Details
- Written by Ranjeet
I have been thinking to write blog on PowerShell too but couldn’t start. Let me start with an article on interview questions which can be asked in format of One Vs Other. Sometime to understand the features of two similar command or topics one vs other is best way to understand. I am listing best possible questions which can ask in interview. Later I will provide answer of these questions in separate article.
- Write-Host Vs Out-Host
- Write-Host Vs Write-Output
- more Vs –paging
- foreach Vs foreach-object
- Out-String Vs Select-String
- Where-Object Vs Select-Object
- Select-String Vs Select-Object
- Where-Object Vs –pattern
- Text based shell Vs object based shell
- -Format Vs -uFormat parameter in Get-Date cmdlet
T-SQL: Truncate table Vs Delete table queries in sql server
- Details
- Written by Ranjeet
Today I thought to write a small article on just to outline the difference between TRUNCATE and DELETE sql queries. It is a very common question that every DBA or Developer know about. But still want to let reader know about it because sometime people don’t put focus on these small questions and loose the important pointers about this. Also trust me even after having more experience these questions is being asked as it is very crucial for some of the features in SQL Server. It may impact some high availability or other SQL Server features. I will point out differences in numbers:
- Truncate is a DDL where Delete is DML queries
- Truncate remove data on table level, we can’t remove selective data whereas Delete queries can work on row level
- Truncate doesn’t log the activity in T-Log whereas Delete logs
- Truncate can’t be rollback whereas Delete can rollback
- Truncate is faster as it just de-allocate the space whereas Delete is more resource expensive as it log the deletions
- Truncate release the space to database engine Whereas Delete won’t
- Truncate can’t be restored whereas Delete can as it can be rolled back
- Truncate reset SEED the identity column values whereas Delete won’t even if delete all the rows from table
- Truncate statement won’t fire trigger to execute where Delete fire trigger
- Truncate won’t be used if table is mark as replication where Delete supports replication
- Truncate table won’t be used in foreign key reference whereas Delete can
MIRRORING: how to manage heavy transaction or high volume log generation process in mirroring
- Details
- Written by Ranjeet
Recently I came through one question for initial checkup of my knowledge on mirroring to process my profile to get new DBA assignment. The question was what is the best approach to manage high volume log generation operation in mirroring? Or other way have you implemented the scenario where mirroring is being paused for principal database maintenance and then how to synch mirror database after starting mirroring again.
It was my day-to-day work in my previous project assignment and worked a lot on this type of different scenarios of mirroring and always on. Let me share the answer of this question with you.
Mirroring best practice setup
- Always setup log shipping in parallel with mirroring
- Keep restore job in disable state at mirroring server
- Make sure log backup and log copy job is running
SQL Server: The request failed or the service did not respond in timely fashion
- Details
- Written by Ranjeet
Today morning, I encountered with below error:
The request failed or the service did not respond in timely fashion consult the event log or other applicable error logs for details
In event log I saw below error:
FCB::ZeroFile(), GetOverLappedResult(): Operating system error 19(The media is write protected.) encountered.
I had same error in past and it was password issue. So, on the basis of this error, we started checking service account password and access issue of the account. We restarted the service after resetting the password and providing the admin permission of server. Although service account password was not changed for a while and had same permission on the server. So doing this didn’t helped.
This SQL Server is running of VM machine. If we see event log error message carefully. It clearly saying about Operating system error. The media is write protected. To get resolved, we escalated it to VM team. They did some fix on OS level by mapping drive. After that it got fixed.
Learning: Just after getting first error. Don’t jump directly into password issue. Must read the event log carefully. That will clearly tell you either it is password issue or something else.