October 3, 2017 at 1:26 am
Hi all,
I have one large database. Recently there are some problems running some reports. I gave the vendor the backup of the database but the third party vendor insisted that the tuning has to be done at the actual server where the database is hosted. Is this true? I finally gave in and allow them to access the server directly. They manage to resolve the issue. When I compare the database before and after the tuning, the only difference is that they added statistics to some of the tables. My question is that is it a must for this to be done at the actual server or can be done at their end. Thanks.
October 3, 2017 at 2:02 am
It depends. I suppose they may have looked at tempdb or at the execution plans. However, they ought to specify why they need access to the whole server.
John
October 3, 2017 at 2:07 am
Hi John,
Thank you for your reply. So by looking at the execution plans, they created the statistics. Is this correct? What does the statistics do?
October 3, 2017 at 2:17 am
I'm not saying that they necessarily looked at the execution plans at all - just that that may be a reason why they insisted on access to the server.
Statistics are sets of data on columns and indexes that contain information on cardinality (number of rows), distribution (how many times values occur) and other things. They can be created either manually or automatically.
John
October 3, 2017 at 2:21 am
Hi John,
Thank you for your replies. I just do not want the vendor to access the servers directly and I also want to know what the did. Thanks again.
October 3, 2017 at 6:40 am
I think it to be a bit odd that you don't want to give the vendor temporary (and, hopefully) very limited privs to your system but you were entirely happy with sending them a copy of the entire database.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2017 at 9:20 am
zulkefli - Tuesday, October 3, 2017 2:07 AMHi John,
Thank you for your reply. So by looking at the execution plans, they created the statistics. Is this correct? What does the statistics do?
The SQL Server optimizer uses statistics to determine the best way to execute the queries people want to run. Within them is information about the range and distribution of values within a column of a table. Here's a good article to start learning about them:
https://www.red-gate.com/simple-talk/sql/performance/sql-server-statistics-basics/
Having good statistics in your databases and keeping them up to date is vital for a well running database. SQL Server will usually build its own statistics when it thinks it needs them (if you have the database option Auto Create Statistics set to True), but you can create your own as well. Once you have large tables, keeping statistics up to date can me more of a challenge though:
https://littlekendra.com/2016/04/18/updating-statistics-in-sql-server-maintenance-answers/
October 4, 2017 at 8:46 am
I would not give my whole database to third party vendors.. Test in Dev/QA/Test environment before implementing any changes in Prod. If issue is not replicated to other environments and only affecting prod. environment than make sure that you document any changes they do in order to back-out if required.
October 4, 2017 at 6:24 pm
Hi all,
Thank you for all the replies. The third party vendor is the developer for the application accessing the database. We have agreements signed to protect the privacy of the data and for them to provide support of the application. As pointed out by Jeff, I have no issues giving them access directly to the server but the question is that is it a must. Why they cannot load the database at their end to troubleshoot the problem? They sometimes requested me to restore a copy of the database for them in the production server stating that to troubleshoot, it must be on the production server. I have the feeling that these people are plain lazy and wanted everything to be laid out for them to work. As pointed by Rpsql, I would prefer them to troubleshoot at their end and pass me the patches need to be done at the production server so that I am aware what are the changes done to the database.
October 4, 2017 at 7:25 pm
zulkefli - Wednesday, October 4, 2017 6:24 PMHi all,
Thank you for all the replies. The third party vendor is the developer for the application accessing the database. We have agreements signed to protect the privacy of the data and for them to provide support of the application. As pointed out by Jeff, I have no issues giving them access directly to the server but the question is that is it a must. Why they cannot load the database at their end to troubleshoot the problem? They sometimes requested me to restore a copy of the database for them in the production server stating that to troubleshoot, it must be on the production server. I have the feeling that these people are plain lazy and wanted everything to be laid out for them to work. As pointed by Rpsql, I would prefer them to troubleshoot at their end and pass me the patches need to be done at the production server so that I am aware what are the changes done to the database.
There may not be any patching or updating of the code to be done. It depends on what the actual problem was. Take statistics, for example. John described what statistics are and Chris provided a couple of links. If the vendor created a few new statistics, that's all well and good, but did they update the ones that were already in place? If they did, then you'd have different row estimates that you could see in the execution plans. Without knowing more, that's my suspicion. If they created new ones, I'd ask if auto create statistics is turned off on that database.
On that note, what database maintenance do you have in place on the server? Specifically, what's being done for maintenance on that specific database? I ask because if the statistics no longer accurately represent the distribution of the data, then your queries are going to suffer.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply