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 8 Most Effective Games and Apps to Learn to Type Fast 2 15 Organization Apps to Boost Your Personal Productivity 3 10 Best Calendar Apps to Stay on Track in 2019 4 7 Clever Goal Tracker Apps to Keep You on Track in 2019 5 How to Type Faster: 12 Typing Tips and Techniques

Read Next

Advertising
Advertising
Advertising

Last Updated on September 11, 2019

8 Most Effective Games and Apps to Learn to Type Fast

8 Most Effective Games and Apps to Learn to Type Fast

Computers and cell phones have become an integrated tool in our professional and personal lives that the original methods of using pen and paper may not be so common anymore.

Although our old-school methods of note taking may not have entirely left us, technology is advancing with no intention of slowing down; iPads are moving into service industries, video calls are taking the place of in-person interviews, and store receipts are making its way into our email inbox – all of which requires the skill of typing.

Learning a new skill doesn’t have to be boring and never had to be. Thankfully, there are effective games and apps that can help you learn to type fast with swift precision and accuracy.

Why Typing Fast Matters?

Learning how to type fast is a game changer. In fact, you can save 21 days per year by typing fast!

Although shaving several minutes from curating a long email or texting paragraphs in a text message may not seem to be of great significance, the minutes soon do eventually add up and the long list of tasks then evolve into frustration. By the end of the day, time is being wasted, and the work pile is stacked high over your head.

Why not alleviate some of those frustrations through practice and dedicating your spare time to build muscle memory?

Learning a simple skillset like speed typing can drastically improve other essential areas in life including time-management and prioritization. Not only does it help you efficiently complete tasks at work and in your personal life, but it also boosts your productivity.

8 Most Effective Typing Games and Apps

Everyone learns at different speeds and uses various methods. While some work better under pressure and tight deadlines, others thrive when given ample amounts of time to learn and soak in the knowledge that is being provided. Despite the number of resources that are available in the hollow corners of the internet, it’s all about finding one source that helps you learn at your fullest potential.

Whether you’re a keyboard ninja or not, here are some effective typing games and apps that allow you to test your speed, accuracy, and maybe shoot some spaceships along the way.

Advertising

For Beginners

1. Speed Typing Online

    What’s more fun than to type to the story of Alice in Wonderland or the lyrics to “Hey Jude”? Speed Typing Online is an online typing game that allows you to dive into the creative and familiar world of famous books, fables, songs, and even hone your skills in data entry.

    The bright blue frame holds the text, which then turns green after punching in the accurate keystrokes. After the end of the personal timer, a statistics page appears to show you your typed words per minute, accuracy, correct and incorrect entries, and error rate.

    2. Typing Trainer

      Typing Trainer

      is another online platform suited for beginner typists looking for step-by-step lessons. Learning the keys on a keyboard can confusing especially for those who aren’t as familiar or getting adjusted to typing on a computer keyboard.

      Typing Trainer has a collection of step-by-step tutorials that covers everything from sentence drills, introduction to new keys as the lessons progress, and skills test. The Typing Trainer specifically highlights unique features in each lesson including a warm-up section where the user begin to build muscle memory and learn to type without looking at the keyboard.

      The website is also programed to identify difficulties the user is facing when typing specific words or sentences.

      3. TapTyping – Typing Trainer

        There is the feeling of physically typing on a keyboard and then there’s the feeling of typing on a touch screen mobile device.

        Advertising

        Since the use of cell phones has become closely integrated into our everyday lives, learning to type on a mobile is much of a skillset as it is to type on a computer. The mobile typing app, TapTyping – Typing Trainer, allows users to practice while on-the-go making it perfect for commuters who want to practice typing during their down time.

        The app allows you to challenge other typists around the world with TapTyping’s global leaderboard and test your skills by taking advanced lessons. There’s always room for improvement and with the app, you’ll be able to find your mistakes by watching a heat map of your finger strokes.

        For professional writers and programmers

        4. The Most Dangerous Writing App

          Suitable for writers facing a creative block or on a tight-deadline, the Most Dangerous Writing App is a website that forces your fingers to type as quickly as your ideas.

          If you stop longer than 5 seconds, everything you had written will slowly disappear from the screen.

          Sessions are timed from 3 minutes to 20 minutes, or can go from 75 to 1667 words. This online app is perfect to brain dump ideas, write a chapter of a manuscript you’ve been stuck on, or help with procrastination.

          If you’re up to the challenge, try the hardcore mode – an alternative option where a single letter appears on the screen at a time. This level prevents you from seeing the entire word, sentences, or even correct any spelling or grammatical mistakes until the timer is complete.

          If you’re wondering, copying and pasting is not an option until each the end of each session.

          5. The Typing Cat

          Advertising

            Looking to upgrade your typing skills? Also working as a personal tutor, the Typing Cat has a list of regular typing courses with the option to try other lessons with more complexity such as HTML. Learning to type code is a another valulable skillset worth adding.

            Even with disregarded interest in the coding world, using the code course enhances your typing skills and allows your fingers to familiarize itself with uncommon word combinations and placement of punctuations on a keyboard.

            The coding course can be difficult even for typing whizzes, but it’s all a part of muscle memory. According Psychology Today,[1] only a handful of people actually learn how to type by looking at an actual keyboard, while a majority of the population locate specific keys intuitively through muscle memory.

            Available courses include EcmaScript 6, HTML 5, and CSS 3.

            Fun typing games

            6. ZType — Space Invaders Meet Webster

              Remember playing the iconic 70’s game that allowed you to shoot tiny purple and green aliens from one end of the screen to the other with a two-bullet laser? It’s hard to believe that Space Invaders just turned 40 , but you can still get the same adrenaline rush with ZType, a typing game with the same shooting concept.

              Ztype works in waves – stages that must be cleared but instead of aliens, you must type out the words before the missiles destroy your ship at the bottom of the screen. Every so often, longer and mor complex words would appear and if the words are not typed in the allotted time, a series of letters will disperse like missles.

              The game is quick on the fingers and will still have your heart pumping until the very end.

              7. Epistory – Typing Chronicles

              Advertising

                Although this game does cost money to purchase, it is worth the investment if you’re looking for a refreshing and alternative mode to learning how to type fast.

                Epistory – Typing Chronicles is a role-playing action and adventure game of a young girl riding a fox in a magical and fictional realm; together they combat enemies in the shapes and forms of words.

                Once you’re starterted, you almost forget you’re playing a typing game. The paper craft art aesthetics of the game has you captivated by the vibrant colors and character’s storyline, while having you build your typing skills.

                8. Daily Quote Typing

                  Need some inspiration? Say no more.

                  Daily Quote Typing is one of many gammes available on Wordgames.com – a website that offers a variety of typing games ranging from different levels based on your experience.

                  With Daily Quote Typing, users are able to type out inspirational quotes by famous leaders, inventors, and innovators such as Mark Twain and Albert Einstein.

                  Bottom Line

                  At the end of the day, discipline and patience is what teaches to type faster. It comes down to making that commitment to improving not only your typing abilities, but in a lifelong skill that benefits other areas in life.

                  By practicing daily and using effective games and apps, it’s only a matter of time before keystrokes will become second nature and your brain will adapt to learning other skills faster.

                  Featured photo credit: Unsplash via unsplash.com

                  Reference

                  Read Next