Advertising
Advertising

10 SQL Server Performance Tuning Tips to Make Your Database Soar

10 SQL Server Performance Tuning Tips to Make Your Database Soar

Regular maintenance and upgrades are an essential parts of ensuring that databases and servers maintain their usability. Many common practices come with a high price and low results, and many developers have begun using alternative methods to improve the performance of their server.

Query tuning can fix queries that have been poorly written and indexes that are not efficient. Use these 10 tips to improve your database in a snap.

1. Separate Data and Log Files

This practice is often overlooked for no good reason. The data and log files should be separated into different physical drives arrays whenever possible, especially when using DAS or SAN. The purpose is to separate the random access of data from sequential access, that happens when writing transaction logs.

Advertising

Sometimes, drives are carved from the same larger disk array rather than different physical drives, which defeats the purpose of separating them.

2. Limited Data Selection

A query will run faster when there is less data to be retrieved. Do as much filtering that you can on the server’s end. Less data will be sent and results can be seen faster. For example, even the smallest changes like removing the term “City” from “New York City” will improve the SQL server performance.

3. Don’t Shrink the Data Files

Shrinking data files is a bad practice in general and can impact performance in a few ways, including fragmentation and causing the queries to suffer. If Instant File Initialization isn’t turned on, the resulting growth can negatively impact performance and cause timeouts. There may be times when shrinking data files is needed, but be sure to know the impact before you take action on it.

Advertising

4. Database Statistics

An optimizer will analyze the statistics and choose the least expensive path, in order to satisfy a query. Statistics that are outdated or missing will cause a prolonged response time because the path chosen will be less optimized.

5. Predetermine the Expected Growth

You can minimize the negative effect of indexes by specifying the appropriate fill factor value when indexing. When the value of a table is changed, the database has to reorganize the data stored to account for the new rows. If you are expecting regular additions of new rows, you can specify this growth for an index.

6. Alter the MAX Memory Limit

64-bit versions of SQL Servers have seen recent improvements in terms of allocating memory and sharing with an operating system, but leaving the MAX memory setting at default is not ideal.

Advertising

You should set the MAX memory setting at a minimum of 1 or 2GB less than the total amount of memory on the server. How much memory you leave will depend on the programs that you have running and how much memory those require.

7. Stick With Single Changes

Rather than making many changes at one time and risking the effectiveness of your query, aim to find the most expensive queries and then adjust the query based on this. Making small changes one at a time will more likely produce optimal results.

8. Before Loading Data, Drop Indexes

This will aid the insert statement in running faster. Once the inserts are done, you can remake the index. Use a temporary table to load data if you are going to insert thousands of rows in a system. Moving data from one table to another is much quicker than loading it from an external source.

Advertising

9. Utilizing Constraints

Advanced query analysis can benefit from knowing and using constraints. Avoid overlapping or duplicating indexes by reviewing existing indexes, keys and constraints. Information for existing indexes can be accessed by running sp_helindex.

10. Extra Columns

When there are extra columns within the database, the system will perform poorly. This is because, it becomes increasingly difficult for the execution of the process to use specific operations of the index. To find any additional columns, use the SELECT* or scalar functions to closely examine the query.

More by this author

Sasha Brown

Seasoned Blogger

11 Obvious Signs He Wants to Marry You 11 Signs He Wants to Marry You (Even You Are at the Early Stages) 11 Must-Follow Natural Health Blogs for 2017 11 Must-Follow Natural Health Blogs for 2017 11 Tools to Help You Keep Track of Your Remote Employees 11 Tools to Help You Keep Track of Your Remote Employees 7 Ways to Effectively Cope With Emotional Stress Seven Ways to Effectively Cope with Emotional Stress 10 amazon review sites that will get you really good deals 10 Amazon Review Sites That Will Get You Really Good Deals

Trending in Technology

1 15 Great Macbook Accessories To Improve Productivity 2 20 Best Mac Apps for Productivity You Need in 2020 3 7 Best Outdoor Security Cameras For Better Home Security 4 10 Best VPNs to Browse the Internet More Securely 5 10 Best Monitors for Your PC Under $100

Read Next

Advertising
Advertising
Advertising

Published on November 19, 2020

15 Great Macbook Accessories To Improve Productivity

15 Great Macbook Accessories To Improve Productivity

When looking to improve your workflow, you need to have a proper computer. And one of the most appealing computers out there is macbooks. These sleek transportable computers allow you to work anywhere at any time and provide a lot of functionality to their users.

But sometimes, that’s not enough. In so many cases, you’re looking for more.

While you could go for some productivity apps, there are actually a lot of macbook accessories available that will make your life easier in many ways. These extra accessories will allow your macbook to charge faster, hold more data, or keep it safe and protected while you travel around.

Beyond that, there is a wide variety of accessories to pick from as many companies have jumped onto the bandwagon of supply to macbook users. Below we’ve listed off some of the greatest macbook accessories that’ll benefit your computer.

What Makes For Great Macbook Accessories?

Before getting into the list, we wanted to go over the criteria for what makes for great macbook accessories. The items that we’ve picked from this list will have the following criteria:

  • Reasonably priced – Accessories themselves shouldn’t break the bank compared to the large initial investment of a macbook.
  • Usability – All these accessories are practical in various situations, however, they also have extra features that make them useful in a few situations beyond their obvious functions.
  • Compact – Workflow is also about space in most circumstances. As such, anything that’s too bulky doesn’t help much. These accessories are either large on purpose – for storage of other items – or are nice and compact so they won’t get in the way.

A USB Drive To Store Files Quickly Without Accessing The Cloud

    While you can always access the Cloud at any time, sometimes accessibility can be cumbersome or it takes time. Instead of trying to access files that way, getting a USB flash drive can save you plenty of time. Especially if your work entails providing presentations.

    The SanDisk Ultra Dual Drive is great as it offers USB-C and USB-A connectors allowing you to plug into virtually any device and transfer files and more between computers. Also, USB drives boast several hundred gigabytes these days making them solid storage devices.

    Buy SanDisk Ultra Dual Drive here.

    Compact Charger That Does More Than Charge

      Your default charger that comes with your MacBook is a good option for a charger, but these days chargers can offer so much more. This can help you speed up your workflow as you can be charging other devices or using the charger for other uses beyond charging.

      Advertising

      In the case of the Anker PowerPort charger, it is more compact than the default charger. It also offers another USB-C connector allowing you to rapidly charge another gadget with it.

      Buy Anker PowerPort charger here.

      Port Adapter For Those With Multiple Devices

        One of the hard restrictions with a macbook is that it only has two USB-C ports. This is manageable for people with fewer gadgets but if you’re the kind who has several, it can interrupt your workflow if you need a particular device and you can’t charge it or access it.

        In this situation, a port adapter from Satechi can help. It offers multiple USB-C ports allowing you to use multiple devices. This can also help in organizing cords which we know can disrupt work. If the USB-C ports aren’t enough, it also comes with an SD card reader and ethernet cable port too.

        Buy Satechi Multi-Port Adapter here.

        A USB Cable That Isn’t As Intrusive Or Cumbersome To Use

        The USB-C cable that comes with Macbooks is small and is used to hook to an external monitor. By adding more length to them, you can have better cable management. One option that we found to be ideal was the Nomad Kevlar cable which offers enough length to do what you want while giving you more space to work with.

        Buy Nomad Kevlar USB-C Cable here.

        Screen Cleaning Your Laptop To See What You’re Doing

        Dust, particles and splotches are to be expected on laptop screens after a while. While you can work around these, sometimes they can obscure your vision at times. The same can apply to all devices these days.

        The only problem with many solutions on the market is that they can damage screens. Fortunately, we found a screen cleaning solution that doesn’t harm screens. ScreenDr Professional screen cleaning kit comes with a spray that’s safe for screens and an antimicrobial microfiber cloth to ensure you make the most of the spray.

        Buy ScreenDr Professional Screen Cleaning Kit here.

        Advertising

        Card Reader That Speeds Up Photo Transfer Time

          If you’re looking for a standalone card reader for your work, getting a device designed for photo transferring and reading is essential. One of the best macbook accessories in this category we found was the Vanja card reader.

          This one is able to read SD and MicroSD cards while also transferring the pictures to a computer with ease thanks to its USB-C and USB 2.0 port options. It’s also small making it easy to carry around and is cheap to buy making it a great choice to pick up if you deal with plenty of pictures.

          Buy Vanja Card Reader here.

          Protective Sleeves To Protect Your Laptop From Harm

          For those who are travelling the last thing that you want is your laptop to get damaged for whatever reason. Even though you’re not likely to drop it as frequently as your phone, having a sleeve for a laptop to be in can save you time.

          One sleeve that we found interesting was the Native Union sleeves. They are made of tear-resistant material with genuine leather accents. Its interior is soft as well preventing scratches from your laptop. It also offers a pocket area where you can store cables and other macbook accessories easily.

          Buy Native Union’s laptop sleeve here.

          Car Charger For Charging On The Go

            For the person always driving around, a car charger is always a helpful tool to have when you’re on the go. The one from Anker is particularly helpful in a number of ways. Not only can this charge your macbook, but it also has an additional port for you to charge your phone if needed.

            It’s not the fastest charger out there when compared to the Macbook Pro adapter but it’s the highest in the market with an output power of 30 watts. For comparison, the second-fastest charging connector offers an output of 18 watts. Aside from that the charger has an illuminated LED ring for you to see it and the connectors late at night. It also has a function that ensures devices don’t experience surges or overheating from the charger itself.

            Buy Anker PowerDrive car charger here.

            Advertising

            USB-C to USB-A Adapter Grants Access To Older Models

            If your older macbook still works but has files you want that you didn’t back up that you want this makes it easier to access them. It also allows you to access devices that have USB-A slots only. This can save you a lot of time if you are moving between older and newer devices.

            Buy Thunderbolt Adapter here.

            A Hardshell Case For Added Protection

            A sleeve is good for travel, but when you’re using it, having an extra layer of protection can be nice. Beyond that this hardshell case can also serve as additional protection from scratches and bumps too.

            Buy Incase Textured Hardshell case here.

            Alternative Sleeve For Those With Tighter Budgets But Want Protection

              If you are looking for an appropriate laptop sleeve but aren’t looking to spend too much, AmazonBasics brands are a good alternative. Their sleeves are elegant and still offer good protection from scratches as well. It also has a few pockets for small essentials too.

              Buy AmazonBasics laptop sleeve here.

              A Hub That Accommodates Several Ports At Once

              We’ve already mentioned a port before being a huge time saver thanks to it having various ports for different devices. This one is a great alternative as while it doesn’t offer as many USB-C ports, it offers more variety. Paired up with the design being the same color as the MacBook Air, it offers two USB-C connectors, two USB 3.0 ports, an HDMI port and SD and microSD card readers.

              Buy Satechi Type C Pro Hub here.

              Earbuds To Deal With Fewer Headphone Cords

                Cords are a common problem when boosting productivity at the desk and one way to mitigate them is to go wireless. If you are thinking about that, one option to consider is the Soundcore earbuds. These are cheaper than the traditional AirPods from Apple which can save you money.

                Advertising

                Furthermore, they deliver great sound and are connected via Bluetooth. The beauty of these earbuds is they can also cancel sound despite them being so compact. They also offer touch control allowing you to pause, play, skip music and answer calls.

                Buy Soundcore Wireless Earphones here.

                Desktop Stand To Save Desk Space

                  Desk space is another crucial part of productivity as things can get in the way and obstruct your work. In cases where you don’t need your laptop at the moment, having a stand where you can set it aside can be helpful.

                  In those situations, we recommend the Twelve South Book Arc. This neat Macbook accessor can save a tonne of space since it requires you to close your laptop in order to insert it into the stand. It also keeps it elevated from the surface which can protect it from spills

                  Buy Twelve South BookArc desktop stand here.

                  A Comfortable And Easy To Use Mouse For Faster Working Speed

                  The last of the macbook accessories we’ll talk about is a proper mouse. Logitech offers fantastic mouses and their Logitech MX Master 3 is one of the greatest wireless mouses out there. It’s ergonomic design, customizable buttons and insanely precise movement is fantastic. It also comes with a scrolling wheel for precise scrolling.

                  The battery power can last for months between charges and like all Logitech devices, it comes with a USB-C connector for charging.

                  Buy Logitech MX Master 3 Wireless Mouse here.

                  Bottom Line

                  There are many macbook accessories out there to improve your workflow. Taking the time to do research and invest in some of the best ones though will save you plenty of time, money and energy in the long term. At the same time, all of these accessories can help you with specific needs for whatever you are doing.

                  Featured photo credit: Viktor Ritsvall via unsplash.com

                  Read Next