New answers tagged

6

First The blocked process report seconds is how many seconds blocking has to go on for before it makes it into the report. I don't have a specific recommendation here, but 10 or higher is usually a good choice. The parameter for sp_HumanEvents is the threshold for what gets shown to you or logged to a table. You are free to set either value to what makes ...


1

The trouble with a RAM disk is that if it fails (or if just turned off), then everything in it just disappears, like the morning mist. You say that your data "will be collected again", but into what? Database != File In order to store data, the database process needs to have a Data File in which to put "stuff" and that Data File has to ...


3

First, I would recommend using enterprise-grade storage devices, regardless of which type. They are significantly more reliable. Your high-traffic usage is going to wear out any consumer-grade device. You haven't described any of the queries that run slowly. In my experience, there's little difference in performance between a fragmented table and an ...


4

InnoDB + SSD ==> no fragmenatation worth noting ==> Don't use OPTIMIZE TABLE. What you claim to experience in this area does not make sense. innodb_buffer_pool_size should be about 70% of the RAM after accounting for your other apps. (1G is too low.) This may be part of the performance issue you are seeing. OPTIMIZE TABLE ==> lots of extra writes ...


0

The DMV sys.dm_exec_requests contains information about currently executing requests. However, it only returns a handle to the SQL text, you need the function sys.dm_exec_sql_text to get the text, and then you need to substring it. select r.session_id, r.command, sql_text = SUBSTRING(st.text, r.statement_start_offset / 2 + 1, (r.statement_end_offset - ...


0

The book is essentially the "PDF version" of Markus' website https://use-the-index-luke.com The website also has the scripts for each DBMS to create the sample schema and test data: https://use-the-index-luke.com/sql/example-schema


Top 50 recent answers are included