SQLAuthority News – Win Windows Phone from Idera in 140 Characters – A Cartoon Challenge of SQL

I personally have Windows Phone and I love it. The user friendliness and integration with social media is remarkable. My wife Nupur is big fan of Windows Live tools and Windows Phone as well. Well, this blog post is not about our preference of Windows Phone but about YOU a unlocked Windows Phone. The Windows Phone will be directly sponsored by Idera.

If you want to win Windows Phone. Just do one thing, complete following cartoon. Every day queries go slow and we think it is SQL Server but the reality is that it is us who need to know the right technology. Idera provides tools for Microsoft SQL Server, SharePoint and PowerShell management and administration.

Contest Rules:

  • Answer must be of maximum 140 character.
  • Winner will get Windows Phone or USD 500 Gift Card from Idera.
  • No purchase is required.
  • This contest is open to all SQL enthusiasts in the world.
  • Recipient will be responsible for local taxes.
  • Idera has all the rights to alter, or modify the competition.
  • To participate please a comment to this blog post.
  • One person can participate multiple times.
  • The contest is open till April 20th, 2011.
  • Winner will be announced on April 25st, 2011.
  • Phone image is for illustration purpose.

Note:

  • Promote at one more social media place the answer to the question.
  • Comments are moderated for spams and SEO marketers, if your comment does not appear, please send me email.
  • Mentioning Idera in answer is encouraged but not necessary.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

212 thoughts on “SQLAuthority News – Win Windows Phone from Idera in 140 Characters – A Cartoon Challenge of SQL

  1. Hi,

    Original reply of this employee in the cartoon was : “Create Index On It” :D

    However,as per I have read,I think in performance tuning(along with indexes)–Logical Query Processing, Efficient Join Techniques,Query Tuning etc. can help… :)

    Jeet.

  2. Rather than guess at why a given query is performing slow, thousands of scans, putting your I/O through the roof, you can use the execution plan to identify the exact piece of SQL code that is causing the problem……

  3. 1) Update Statistics which are out of date.
    2) Create missing indexes and drop un-used Indexes.
    3) De-Fragment Indexes which have fregmentation > 10 %.
    4) Insert appropritate Fill Factor, if it is an OLTP database
    5) If feasible remove Joins and use sub-queries instead.
    6) Use Wait Types and Queues to Get Quick Performance Bottleneck and resolve accordingly.

  4. Check the indexed views. Redesign the query if necessary. Update outdated statistics. Check network communication. Do partitioning. Check/Create indexes.

  5. It is ideal case for Query Performance or Server Performance.
    1) It can be case that everyday the query is running perfectly but today only it is running slow.
    Then in this case, we need to check server Performance with DMVs, perfmon with specific counters, Task manager and others to find out possible issues with Memory, Disk, CPU related congestion.

    2) If query is running slow daily, then it can be problem with query, statistics, execution plan, index fragmentation and so on..

    Depending on the case, the troubleshooting has to commence.

  6. Look for the execution plan and the flow of execution , query
    hints ex: option(force order) might help solve the problem.But be cautious ..

  7. Following reasons may cause to queries slow.

    1) sql server,OS, Network and System configuration is not proper.
    2) DATABASE DESIGN is poor.
    3) Query Planning is poor.
    4) Missing Indexes or Wrong Indexes.
    5) No proper additional statistics.
    6) No Partitioning (for large volume data).
    7) No data striping.

  8. 1.Look for SQL Server wait stats
    2.Find the most expensive queries and try tunning them .
    2a.Look for query statistics
    3.Look for plan recompilations.
    4.Look for update Statistics options if necessary.
    5.Check for queries taking parallelism into their work flow
    taking the system into account i.e OLTP or OLAP

  9. 1) Enable Database indexing
    2) Check for the server statistics
    3) Check for available hardware resources
    4) Try to make your query simple by adding additional computing.

    Above steps will surely improve performance

  10. Are queries performing slower then given benchmarks? If you are not adopting a proactive approach and it’s just a random guess that queries are performing slow, then possibly, we have to do lot of RANDOM things to rectify the problem. 99% such problems occurs when you are not prepared and do not watching your database closely. Must set performance benchmarks, must have proper tools and scripts in advance and always adopt proactive approach.

  11. Try following Things:
    1. Update statastics
    2. Rebuild Indexes and drop not reqired Indexes
    3. Check the joins and use effecient Joins.

    If Problem is still there then we need to analize the issue using profiler and can suggest the solution.

  12. 1. Properly manage indexes.
    2. Only usable indexes placed.
    3. Remove unwanted indexes which will hold the unnecessary space of server.
    4. Use sql server 2008 R2 for use the feature of parallellism for better performance.

  13. 5. Also show the excecution plan and try to remove Sort, Key Lookup, Table merging. Which will help you to improve the performance of query.

  14. Did you try
    Index
    Defrag.
    Execution Plan
    Remove unnecessory joins
    Analyze with Tuning Advisor ?
    Use IDERA to make Query Results faster

  15. well in order to make query efficient or to speed up the query adopt the following ways:-

    1. use different type of indexes.
    2. properly locate different DB objects across different tablespaces, files .
    3. In last drop all those indexes which are of no use.

    • well in order to make query efficient or to speed up the query adopt the following ways:-

      Logical Query Processing
      Efficient Join Techniques
      Query Tuning Considerations
      Avoiding Common Performance Tuning Issues
      Statistics and Best Practices
      TempDB Tuning
      Hardware Planning
      Understanding Query Processor
      Using SQL Server 2005 and 2008 Updated Feature Sets
      CPU, Memory, I/O Bottleneck
      Index Tuning (of course)

  16. Hi,

    I highly recommend Idera to optimize the databases. It increases performance and optimization rate is good. Infact, I have tried several methods, but My Trainer recommend Idera.

  17. “Got no idea? You need Idera! We’ve got just the right tools for all you SQL fools.Performance tuning, monitoring, backups or development.We’ve got you covered.”

    160 exactly :-)

  18. Answer: “No they don´t! I fixed them on the way with Idera SQL mobile manager while on the way to meeting.”

  19. 1. Review the query, it is using index or not
    2. Create proper index
    3. Make query to use proper index
    4. Rebuild index, if required and it is using index already
    5. Review exectution plan for query, if it is using joins, to get some idea to write better join or another way to write qeury

    Thanks, Virul

  20. Queries Slow, Call the SQL Doctor. Queries still slow, call the SQL Server Superhero with his Diagnostic Manager power skills. With his x-ray query vision, he will remedy the situation in no time.

  21. Lot of reasons for query to run slow. Troubleshoot manually may tak a day bt I hav SQL Doctor, I will fix it Just Like That while u hav a coffee

  22. Check your index for the columns used in where clause.
    Put only numeric fields in the where clause if possible.
    Create indexes for these.
    If still slow call Pinal to fix the issue.

    Thanks
    Shyam

  23. -Slow network communication.
    -Inadequate memory in the server computer, or not enough memory available for SQL Server.
    -Lack of useful statistics
    -Lack of useful indexes.
    -Lack of useful indexed views.
    -Lack of useful partitioning

  24. Slow network,.
    Inadequate memory in the server,
    L of useful stat.,
    L of useful indexed v,
    L of useful data striping, and L of useful partit.

  25. 1. Look for index fragmentation from: sys.dm_db_index_physical_stats.
    Based on the results displayed, reorganize or rebuild indexes

    2. Update your stats

    3. Look at the execution plan and create new indexes & stats if necessary.

    4. Find out the exact reason for your slow query, if its works good today and works slow on the next day, then check if you have any job or process which is populating data into the table and see if you have any fragmentation using the DMV mentioned in setp 1.

  26. Hi..

    1) reduce use of join and possibly use sub-queryies
    2) Manage indexings
    3) if possible keep most frequent required table data as less as possible,by making archived dataTable. same for “isDeleted” Flagged. it will help to retrieve routine records faster.

  27. Let Idera figure it out the exact health/condition of the system. Meanwhile lets discuss taking uour business one step further over a cup of coffee.

  28. That’s why i was suggesting to Hire Pinal Dave.
    Now first Analyze the Queries then make a strategies to overcome the problem with in given time/resources

  29. Check the below listed reasons for slow-running

    * Slow network communication.
    * Inadequate memory in the server computer, or not enough memory available for SQL Server.
    * Lack of useful statistics
    * Lack of useful indexes.
    * Lack of useful indexed views.
    * Lack of useful data striping.
    * Lack of useful partitioning.

    “if any of reason makes your query works slow work on it ,Otherwise problem in your query – check your query”

  30. -Give Large size to Database at the time of creation instead of default size 2 mb give it few GB so that at the time of insert it need not to take time to allocate new space
    -Normalize your tables
    -Use cursors wisely
    -Index frequently used Columns, don’t use to much indexing
    -Increase timeouts
    -Always qualify the full list of columns in SELECT Query.
    -Go through Pinal Dave’s Guidline for SQL.

  31. Go to the nearest “SQL DOCTOR”and said him that you are suffering from Lack of Index Optimization.He will prescribe you the right medicine for that.

  32. Optimize your query, Check Database (mirror, replication etc.), SQL Server, Hardware and Network configuration, Other processes.

  33. hi,

    1. Use Apply while using nested queries.
    2. Index the ORDER-BY / GROUP-BY / DISTINCT Columns for Better Response Time
    3. Use Subqueries instead of joins.
    4. Change OR to UNion

  34. slow query can be due to any of the issues..
    – check processes related to the query, blocks if any
    – see execution plan of the query
    – see sql server performance wrt memory, cpu,io. run performance monitor if needed
    – if you have tool like idera DM or sql sentry check thru the tools
    -see waittype of the spid, and proceed further if that is a IO related issue or network related issue etc
    – if the query was fine all the days, and its slow today see if the indexes are fragmented due to some data DMLs done recently, do update statistics of the related tables in an highly OLTP environment without blocking the system for other queries

  35. Try to limit the usage of outer joins.Always keep your indexes as narrow as possible.Also don’t index small tables.So go and Get IDERA.. :)

  36. Not for award but for fun, the reply should be…

    “Yeah, I have heard MS SQL Server is better than other RDBMS and it has various options available to get optimum performance. Looks it time to upgrade to MS SQL Server. Developers have done great job so far but now we should also hire some DBAs.” :-)

  37. Use following products
    1 SQL Defrag Manager
    2 SQL Doctor
    3 SQL diagnostic manager
    4 SQL Check
    Google each product to know more.

  38. Slow query can be due to

    * Poor indexing strategy.
    * Out-of-Date / Missing statistics.
    * I/O subsystem problem.
    * T-SQL code.
    * Database/Table Structure/Schema Design.

  39. 1. “Dedicate more than 256 MB RAM to SQL Server Machine”
    2. “Disable defragmenting disc during working hours”
    3. “Upgrade from SQL 2000 to SQL 2008 R2″
    4. “What do you mean for slow?!!? One hour, one second or two milliseconds?”

  40. “how many times have I told you, its the where clause that you need to build your indexes off of not the select statement.” :)

  41. When you have a problematic query, you have a wealth of methods and tools available to you. You could analyze the query via SSMS, use showplan in Profiler, use DMVs, or just cut to the chase and bring up Idera’s SQL Diagnostic Manager (SQL/dm) to analyze your query.

    I’ve used SQL/dm for many years. It has allowed me to quickly determine the worst performing queries and then dig into them with the developer who created them and make them perform satisfactorily.

  42. First check for Sp_who2 active who is blocking then look for last update statistics,index missing checking,if possible apply include indexes by using DMVS try to avold linked servers in the query to run remote updates etc..

    finally Look for Pinal Dave suggestions site

  43. Can you show me examples compared with your base line running statistics? Once we’ve determined your query alone, and not the entire server, is actually poorly performing then we’ll need to either think about re-writing it in a more efficient manner and also consider applying an index strategy to support it. A stored procedure may even be in order.

  44. Beside index and update statistics there are many other reasons why SQL query may run slow.

    For example Disk IO (Raid), too many joints (unavoidable sql joints), use of linked servers (network).

    Type of query also play a role.

    For example, you may have proper index in your tables and select queries are running fine. However, insert or update queries on those table could experience performance difficulties. Heavily used for those tables should have less index.

  45. I know you like to use your star power, but you shouldn’t use * power in your queries. Let Idera ‘s SQL Doctor take a look.

  46. Analyze Indexes/Conditions/Joins/Exec Plan/Profiler/DMV/Stat
    if Applicable,
    Add,rebuild,defrag index
    Use Partition/AWE
    Indexes on Diff FileGroup

  47. 1. Use SQL Diagnostic Manager (Idera) to diagnose performance issues and identify server resource bottlenecks.
    2. Use SQL Defrag Manager (Idera) to manage SQL Database and index fragmentation.
    3. Use SQL Diagnostic Manager (Idera) to track query performance over time.

  48. Pingback: SQLAuthority News – Last 5 Days to WIN Windows Phone 7 – 140 Words to Win Journey to SQLAuthority

  49. Answer should be we should analyze on the below points.

    – use samll result sets
    – dont use Select * instead limit the number of columns
    – try with highly restrictive condition in the WHERE Clause to only get required data
    – Avoid Cursors and try temp tables
    – Avoid using arithmetic operators on the where clause
    – Avoid using Function on where clause
    – Avoid using Exlusion conditions in the where clause
    – use Set Nocount on
    – Check indexes required for the columns and drop unwanted indexes.
    – Check with the profiler on the table scans
    – Check for the outdated UPDATE Statistics.
    – Check for all the Joins and try to avoid if un necessary by using sub queries.
    – If joins are required check for the correct join type and correct order of joins
    – check for the feasibility of Parallelism if using a Multi processor computer.
    – Check for the network connection failures or its slow
    – Check for the partioning of the data.

  50. You may not be a reason for that! Do a review on Query level, Database level, Server level and Network level. Performance is a joint effort.

  51. 1) Optimize your queries , if it has joins
    2) Use DBCC commands, CHECKDB
    can list out all the errors with DB

    and reindexing can make the Query Run faster
    Use DBCC DBREINDEX

  52. - Optimize your queries , if it has joins
    – Use DBCC commands, CHECKDB
    can list out all the errors with DB and reindexing can make the Query Run faster Use DBCC DBREINDEX

  53. 1)Manage Indexing in every respect.
    2)Reduce the unnecessary Columns return,avoid select *,OR,Like Plague,Specify Table Qualifiers,Joins
    3) Inadequate Memory in server
    4)use Useful partitioning.

  54. Hi Pinal,

    My View over “My Quries Running Slow”

    1. The sys.dm_db_index_usage_stats DMV will be introduced as a tool to identify existing indexes which your queries may not be using. Indexes that are not being used will provide no benefit to your databases, but will use valuable disk space and slow your update operations, and so they should be considered for removal.
    2. After creating an index, make sure you verify that the index is, in fact, used in a plan
    3. SQL Queries must be write in manner so they can reuse plans.
    Example:
    Use sp_executesql instead of Exec .
    4. SQL Server does not maintain statistics for XML and spatial data types. That’s just a fact, not a problem. So, don’t try to find statistics for these columns, as they’re simply not there.
    Solution:
    If you experience performance problems with queries that have to search through XML data, or filter spatial columns e.g., XML or spatial indexes may help. But that’s another story and beyond the scope of this article.
    5. Perform Reindexing on a table which is altered recently so that it can use index’s in optimized way.
    6. If possible, re-write your SQL Code so that comparisons are only done with “pure” columns. So e.g. instead of specifying

    where sqrt(c1) = 100

    You would do better to write:

    where c1 = 10000

    7. If you are going to create a index on a table then FillFactor is most important thing if your table rarely have insert and updates then fillfactor can be 0 or 100 if your table can have insert and updates frequently then it should be 80,90…or whatever perfact.

    8. Carefully inspect, if your queries can make use of multi-column statistics. If so, create them manually. You may utilize the Database Engine Tuning Advisor (DTA) for regarding analysis.

    9. Improve your TSQL Code. Avoid using local variables in TSQL Scripts or overwriting parameter values inside stored procedures. Don’t use expressions in search arguments, joins, or comparisons.

    10.Avoid NOT IN, instead use a left outer join – even though it’s often easier to visualize the NOT IN.

    Thanks For this Great Competition
    Thanks Idera Too.

    NOTE:This Article Refers many SQL Web Sites.

  55. • Rebuilt index if they are defragemented
    • use proper joins
    • remove index hints if any
    • minimize the use of string operation, use computed columns instead
    • avoid the use of functions

  56. “Ohhh!! This is a serious problem which can be treated only at Idera. Get to Idera and ask for SQL doctor v2.0. He is an expert in treatment of such diseases”

  57. Let me analyze those queries!
    Tools such as Idera, are available in the market to make things faster & easier. If needed we will go for it.

  58. commonly
    Slow network communication.
    Inadequate memory in the server computer or for SQL Server.
    Lack of useful statistics,indexes,indexed views,data striping,partitioning.

    Check for AUTO_CREATE_STATISTICS,AUTO_UPDATE_STATISTICS are ON

    Use of Database Engine Tuning Advisor

    implement Disc Striping via RAID.

  59. Hi,
    as per the expression the person who has to answer doesn’t care for why it is happening ,

    Therefore my answer is

    ” so what can i do? “

  60. Oh you have the “Common App Person Syndrome”. Take Specificity test for Query, Recent app changes, index, Query plan, Stats and see DBA Doc.

    Sh! I will tell you a MS Certified affordable diagnosis secret! Idera Dignostic Manager! Top Notch

  61. Analyze Query,Using SQl Profiler, Actual and Estimated Execution Plan. After then try to determine the cause of slowness.It can be any of the below mentioned reasons.
    (A) Joins,Indexes,Locking Methods
    (B) Cursors – If it is what affecting query performance, try to replace it with GROUP BY ..HAVING, CASE statements, SUB-QUERY
    (C)Complex Stored Procedures that makes a combined use of DML and DDL statments
    (D) If, there are many complex queries executed on server or if there is high activity on server, may be adding more memory, can improve performance.
    (E)Schema Changes, related to any object – i.e is adding and dropping indexes, constraints etc
    (F)Try to determine Logical Read Write Value,Application Name, I/O Consumption etc.
    There are may tools available in market, that can be used to determine query performance. Apart from this, “It Always DEPENDS”, on how and where Queries are used.

  62. Pinal, Today is the last day, Where is your entry?
    Of course you too eligible for this prize!
    Read the rules, It is for all SQL enthusiasts!

  63. After doing the obvious like checking for index fragmentation and query tuning,
    Just Idera the database and you will be fine.

  64. 1. Check for blockings, if yes, find the culprit spid
    2. If no blockings, check errorlog for network or I/O errors
    3. If above issues not found then dissect the query and check for indexes on the columns used in query. If indexes are there, check for fragmentation. If needed reorganize/reindex the indexes based on the percentage of fragmentation followed by update statistics. If indexes are not there, we need to create them.
    4. If the above conditions are not found, we need to check for waits using DMVs.

    Thanks,
    Amit

  65. 1) Analyze missing indexes with high Avg_Estimated_Impact using DMV. Key lookups, Sorts and table merge reduce the performance.
    2) Determine unused indexes using DMV as they reduce performance of queries involving Insert/Update.
    3) Check the fragmentation on existing indexes regularly. Rebuild if the fragmentation is greater than 40% and reorganize if the fragmentation is between 10% and 40%.
    4) Do not shrink database/ files after rebuilding or reorganizing indexes. It causes fragmentation again.
    5) Turn on AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS which would automatically update statistics after query optimization.
    6) I personally like using Idera SQL diagnostic manager as it effectively helps me in determining bottlenecks evolving from locks, deadlocks, resource Wait Stats & queues, database mirroring and replication.
    7) Proper database storage planning based on traffic. I feel RAID 10 is good for faster reads-Writes and RAID 50 for faster reads.

  66. Hi,

    To Spped Up your Query Have this in your Query
    1) use WITH(NOLOCK) in select statement
    2) Use SET NOCOUNT ON and SET NOCOUNT OFF In begining and End of your query Which will reduce the network Band Width.
    3) Create Proper Clustered and Non-Clustered Indexes On the table
    4) remove Sorting in Query(this Can be handeled in Front End Code Ex : using LINQ in .NET).
    5) using “parallellism” featur In sql 2008 R2 gives better performance.
    6) Run the Query by Enabling the “Actual Execution plan” in SQL Server, Which will suggest What need to be Done for the Query to Speed Up(Ex : Create Non Clustered Index On table) And Also it will Show Which part of the Query is taking Much Time to Execute.
    7) use “client Statics” while running the Query this will give you the “Total Execution Time” by this we can analyse and can write Query in better way.
    8) avoiding Complex joins in Query

  67. 1.Query Plan that Included Nested Loop for many Rows.
    2.Check for Proper Indexes.
    3.Update Statistics.
    4. Check if theres too many joins.
    5.Check for parallelism.
    6. Check if the Query is using dynamic sql.

    these are few of them, there are so many things which we can look.

  68. Let’s just switch to NoSQL — I hear it’s always much faster … *snort* sorry, I can’t say it with a straight face!

  69. I would have looked in the following format.

    1. sp_who2 to running processes
    2. Inefficient query
    3. Run the query execution plan
    4. Identify the issue according to execution plan
    a. Index/Table Scan or SEEK
    b. Columns in query vs Columns in index
    5. Index fragmentation.
    6. Table Statistics
    7. Which version of SQL is it ? If 2008 or above maybe resource governor is strangling the resources.

  70. Check for Joins used.
    Identify area slowing it.
    Check unused Indexs
    check Qry using the indexs.
    use tables filetring more data to be placed Ist.

  71. I forget to tell that it’s not same database as he had yesterday,i just move it to other server and Restored bad one at this server.

  72. As the days advancing its getting bigger with data, so eventually sql server may go slow, so we can use Idera tools to check performance monitoring and if required we can try to use Idera PowerShell development environment and optimize to queries.!!!

  73. I think many contestants forgot these rules..:)

    Answer must be of maximum 140 character.
    The contest is open till April 20th, 2011.

    • For me I did not even consider changes in timezones. I submitted my answer before midnight CST. There was not a clear closing timezone specified. I hope that this does not disqualify those who submitted on here. PS. I also stayed within the 140 character limit :-)

  74. Hi All,

    Thank you for wonderful participation. I am overwhelmed with the participation and your interest.

    I have created list of all the participants to community leaders ( I will post names later). Due to amount of the participation we are facing issues to pick winners.

    The competition is closed and the winner will be announced very soon – early first week of May

    Many thanks again,
    Pinal Dave

  75. Hello Pinal,

    There are many answer which are not as per RULE given. Please ensure before doing further calculation.

    Regards

  76. Pingback: SQL SERVER – Cartoon Challenge – 140 Character Winner is Here Journey to SQLAuthority

  77. Hi,
    There are a number of common reasons for slow-running queries and updates:

    1. Inadequate memory in the server computer, or not enough memory available for SQL Server.
    2. Lack of useful statistics
    3. Lack of useful indexes.
    4. Lack of useful indexed views.
    5. Lack of useful data striping.
    6. Lack of useful partitioning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s