SQLPFE.IL

  • Working with SQLIO and analyzing it’s output

    Introduction While working on a customer request, I faced the question what is the SQLIO? how to operate it and how to analyze its outputs? The customer has read about the tool and was also able to run it but wanted more information on how to do...
  • Four Tips for SQL Tuning for SharePoint Part 3 TEMPDB

        One of the focal points in SQL Server Tuning for SharePoint is around the TEMPDB. A lot of performance issues are caused by the performance of the TEMPDB. TEMPDB is one of the four system databases. Master is the database that contains all...
  • Network bind order warning during SQL Server installation at failover cluster

    Installing SQL Server 2008, 2008R2 and 2012 on failover cluster has been significantly changed from SQL Server 2000 and 2005. Installation is performed separately on each node in order to avoid failures and downtime and much more. One of improvements...
  • Check your SQL – default Trace

    Every installation of SQL has a default trace. The default trace was introduced in SQL 2005, It is the same trace that we know from the profiler or server but it is a preconfigured trace that is enabled as default. The default trace create five *.TRC...
  • SQL & SIDs why we need it and what the hell is it?

    Service SID and Or ice age Sid Lately I was asked about service SID during the installation of SQL 2008 Cluster. So I thought about it and wrote this blog post: During the times of SQL 2005, while installing SQL in a cluster configuration we...
  • PERFMON & SQL Server - a small useful tip

    One of the best Performance tools that I use is the PERFMON. PERFMON is a tool that exists out of the box on every windows machine and has lots of counters for almost every application. Perfmon provides current, accurate information on thousands of...
  • Using the proper login account for the correct SQL Service

    I was often asked this question: I'm installing my SQL Server. What should I use as the service account? There are a few options (Local Account, Service Network, domain User) What is the best and suitable account for the service? I will try to answer...
  • Four Tips for SQL tuning for SharePoint Part 2 - Shrink DB

    SharePoint DB’s - To shrink or not to shrink the data file, this is the question: SQL Server provides you the ability to shrink the DB or shrink the files [MDF, NDF & LDF]. Shrinking the log file/LDF is quite different from shrinking the...
  • SQL Alias PowerShell script

    Hello everyone, this time I am going to show the "Server Alias". I have been asked by one of my customers to write down a PowerShell script to add an SQL Alias as part of an automatic installation. The Alias is stored in the registry at this...
  • Discontinued and Deprecated features in SQL Server 2012

    Recently we encountered a few problems with upgrades to SQL Server 2012, mainly because of usage of features that are no longer supported. Each time we are approach an upgrade, we try to minimize the risk. These built-in tools can assist us: SQL Server...
  • Four Tips for SQL tuning for SharePoint Part 1 - MAX DOP

    Lately I have been thinking about some of my latest work; mainly I have focused on an one day work shop with one of my colleagues, Amir, who is a SharePoint PFE. Now I’d like to share with you some of the SQL Best Practices for SharePoint. ...
  • Did you know , Dead locks can be track from the System Health in SQL 2012

      Before using SQL server 2008, in order to capture a deadlock, we had to create a server side/profiler trace or use trace flags 1204, 1222. If you were lucky enough to work with SQL 2008/2008R2 you could have tried using XEvents (which were introduced...
  • The scenario of a missing SA password:

    You are the senior DBA of your organization, responsible for all the databases that hold the critical info. In order to secure the environment and the databases, you performed the next recommended steps: 1) Remove all built-in admin account from SQL...
  • SQL 2012 System health–fast analysis

    Once we encounter a few problems with the SQL 2012 and we need to analyze it, what will be the first recommended step to start with? During my last post I have discussed the default trace in SQL and its benefits. SQL 2008 introduced a new option called...
  • SQL Server 2012 new feature The Snippets

    Snippets In SQL 2012 we have a new feature called snippets; So what are this snippets? Lots of DBAs, when required to create a new T-SQL command, check out the syntax in BOL, they try to remember on how to do it, Or sometimes we need a repeating T-SQL...
  • the case of the unexplained - how we can get different execution plans and result sets for same query

    The other day, my colleague and a good friend called me and told that her SQL Server gone nuts. One of developers launched a query and it run for ages, once she executed the same query it returned results in a few seconds. So how something like this can...
  • User-Defined Server Roles - new server option in SQL Server 2012

    Along with Organization growth and databases growth we need more specific permissions for DBAs, operations and development teams. Starting SQL Server 2005 we can grant server permissions on a more granular level, for example “view server state” and...
  • Target Recovery Time - new database option in SQL Server 2012

    Recently I’ve created a new database on my SQL Server 2012 instance. I reviewed the new database options before creating. One of the options that I’d like to share today is “Target Recovery Time” in seconds or TARGET_RECOVERY_TIME...
  • HBA, Queue Depths and inflight IO.

      I had recently received a question form one of my customers asking what is the recommended Queue Depths value for the HBA adapter. This question was intriguing and drove me to do a little research on the subject; I’d like to share the information...
  • How to avoid text-to-date conversion mismatches

      In one of our previous posts we saw how different set options can affect execution plans and result sets. Now let's see how to write our code in order to avoid text-to-date conversion mismatches. In this example we can see one string being converted...
  • SQL Server 2012 Audit Enhancements

    SQL Server Auditing is useful for auditing the usage of your database operations. We've got quite a bit of new features and enhancements in SQL Server 2012. First, server level auditing is now available in all SQL Server 2012 editions. Database level...
  • Replication of indexed views

    Recently we created a Transactional Replication for one of our customer's database. In addition to tables, views and procedures we had indexed view including Full Text Index base on it. Currently, we cannot specify on the management studio what we’d like...
  • LOG PARSER.

    Hello everyone, today I'd like to write about the LOG PARSER. First time I have encountered this tool was around 2004 when I had to load a big mass of data (IIS Log) into a database. A month ago I was asked by a customer, how can is it possible to read...
  • Startup parameters in SQL 2012.

    SQL 2012 brings us new options and cool features, one of these features is the startup parameters. On previous versions; when we needed to update a startup parameter for the database, we needed to go to the configuration manager and then in under the...
  • Trace flag at query level is supported

    Have you ever wanted to execute specific query with specific trace flag turned on, without affecting the session or the server level ? If the answer is yes, now you have a hint to support it – "QUERYTRACEON". It been out there for a while, but...