In a recent Office Hours post, David Reabow suggested letting ChatGPT answer a few of the questions y’all posted at http://pollgab.com/room/brento/. Brilliant! Let’s see how it does. I purposely cherry-picked the questions that I thought ChatGPT would have the easiest time answering.
Rollback is single threaded asks: Hi Brent! An update statement has blocked all other sessions. What should I do? Killing it, restarting SQL server service, waiting, etc. What’s your opinion? Thanks!
If an update statement has blocked all other sessions, there are a few options to consider:
- Monitor the situation: If the update is taking a long time, monitor it to see if it’s making progress. If the update is hung, it may eventually clear up on its own.
- Kill the blocking query: If the update is not making progress, you can use the KILL command to stop the blocking query. This will allow other sessions to proceed, but any data changes made by the blocked update will be rolled back.
- Restart the SQL Server service: Restarting the SQL Server service can clear up any blocked sessions, but this should be done as a last resort because it will cause an interruption to all other sessions.
- Wait: In some cases, waiting is the best option. If the update is expected to run for a long time, and there are no pressing issues with other sessions being blocked, waiting may be the best course of action.
In any case, it is advisable to take a backup of the database before taking any action to avoid data loss.
Brent says: bullet points 1 and 4 are basically the same. I abhor bullet point 3 because it doesn’t add anything that bullet point 2 wouldn’t have done. It was on the right track – bullet points 1 and 2 are good starting points.
Peter Riis: Hello Brent. We got a database storing mainly images in BLOB. Storing BLOB’s in the database got several disadvantages, so we thought about implementing FILESTREAM. What are your opinion on storing files in a database?
Storing large files like images in a database can have the following disadvantages:
- Increased database size: Storing large files in a database can significantly increase its size, making it slower to manage and query.
- Slower performance: Querying and retrieving large files from a database can be slower than accessing them from a file system.
- Limited scalability: Storing large files in a database can limit scalability as the database grows in size and complexity.
On the other hand, FILESTREAM is an option that allows you to store large binary data (BLOBs) in a database, while maintaining the efficient streaming access to that data. FILESTREAM integrates the SQL Server database engine with the NTFS file system to store and manage large BLOB data.
In conclusion, whether to store large files in a database or not depends on the specific requirements and use cases of the application. If efficient streaming access to the large files is required, FILESTREAM can be a good option. However, if the focus is on performance, scalability, and managing large files efficiently, storing the files outside the database in the file system might be a better solution.
Brent says: nailed it.
Stone Tablet Monk: When creating a new index, what is your opinion of using OPTIMIZE_FOR_SEQUENTIAL_KEY for minimizing last page insert contention? Is this a good solution? Is this the only solution?
The OPTIMIZE_FOR_SEQUENTIAL_KEY option can be used when creating a new index to minimize last page insert contention, by prioritizing index page allocation for new data rows at the end of the index. This can help reduce index fragmentation and improve insert performance.
However, this is not the only solution to minimize last page insert contention. Other solutions include:
- Increasing the fill factor: The fill factor determines the percentage of space that is reserved on each index page for future growth, and increasing it can reduce the frequency of index page splits.
- Using a clustered index: A clustered index physically orders the rows of a table, so new rows are added to the end of the table.
- Monitoring and defragmenting indexes: Regular monitoring and defragmentation of indexes can help maintain their efficiency and reduce the frequency of page splits.
In conclusion, using OPTIMIZE_FOR_SEQUENTIAL_KEY is a valid solution for minimizing last page insert contention, but it should be evaluated along with other options to determine the best approach for a specific scenario.
Brent says: ChatGPT is simply flat out wrong here. Fill factor is a setting that isn’t honored during inserts. Clustered indexes are irrelevant here, too. This answer illustrates one of the challenges with AI – it can’t synthesize information on brand new terms and topics where there isn’t already conventional wisdom for it to analyze. Now, for the answer: what I’d ask is, how do you know that you have last page insert contention? This typically shows up at over 1,000 inserts per second, sustained – and most people I see asking this question, don’t even have the problem.
Piotr: When are SSMS client statistics helpful in troubleshooting query performance issues?
SQL Server Management Studio (SSMS) client statistics can be helpful in troubleshooting query performance issues in the following situations:
- Long-running queries: If a query is taking a long time to complete, client statistics can provide information on the execution time, wait time, and other details about the query.
- Slow network performance: If a query is running slow due to network latency, client statistics can show the time spent sending and receiving data between the client and the server.
- High CPU or memory usage: Client statistics can help identify queries that are consuming high amounts of CPU or memory on the server, allowing you to focus your performance optimization efforts on the most resource-intensive queries.
- Blocked queries: If a query is blocked by another query, client statistics can provide information about the blocking and blocked sessions, which can help in identifying the root cause of the problem.
To enable client statistics in SSMS, right-click the query window and select “Include Client Statistics” in the context menu. After the query has completed, the client statistics will be displayed in the results pane.
Brent says: again, ChatGPT is just bullshitting here, and most of this is just flat out wrong. Client stats don’t show wait time or blocking queries. As to the real answer – for me, client statistics haven’t been useful.
Here’s the problem with ChatGPT.
It speaks with the same level of authority no matter how much or little confidence it has in its answers. It never says “I think” or “I suspect” or “Perhaps” or “I don’t know.” It just confidently struts right onstage and speaks loudly.
I stopped after four questions because I think these 4 really sum up the problem with ChatGPT today.
If you have tech questions, ChatGPT could theoretically be useful if you already know the right answers, and you can weed out the garbage. But … if you already know the right answers, what’s the point?
I do think AI has tons of good uses, like writing real estate listing text, but answering database questions isn’t one of ’em.