0

I realize how vague this question is going to be, unfortunately I'm at the end of my rope in trying to solve the issue.

My company has a .net 2.0 app we wrote. It uses Microsoft SQL Server for it's database. We have been deploying it with no issue for a long while. Our most recent deployment had the Database installed on MSQL Server 2014, it was originally created on 2005. After the installation of the database, and the installation of the client application, the program runs great for about 4 days. After those 4 days, it takes about a minute to log in (Using ASPNET) After log in some of the data pulls do not work. For example the main screen gathers a set of records based on the user who has logged in, this list won't populate. However if you open the screen for assigning the user to those records, the records and the user assignment populates with no issue. Of course with the weird data problems, the app is running incredibly slow. This also has never occurred. The installation in question is on Windows 7 and 10, both OS's we have installed to before with out incident.

Any thought on a possible direction for troubleshooting this would be appreciated, I have hit it from several angles with no luck.

Thanks again.

So the answer to this problem was to turn on ANSI nulls in all of my stored procedures. Due to how vague my question is I'm not posting this as an answer. However the issue is resolved and I'm adding this comment for the sake of completeness.

ChettDM
  • 325
  • 1
  • 3
  • 10
  • Could be an index issue or sql engine configuration. Does it use Stored Procedures? – Esselans Mar 09 '16 at 17:02
  • Yes, it uses stored procedures for every transaction. when you say index issue, are talking about an index value for each record being present with in the tables? – ChettDM Mar 09 '16 at 17:03
  • 1
    You have to first figure out where the slowdown is coming from. Is it sql server that is slow? If so, how do you know? Are there particular procedures that are slow? Have you evaluated the execution plans for those? This could go on for days as to what you might possibly look at. – Sean Lange Mar 09 '16 at 17:04
  • If from now on the login always takes a 1 min or more. You can focus on that. Check the table from missing index (yes, the one you create manually base on a column, say username o whatever you use to login) view the associates execution plans. May use of sql query analyzer maybe. – Esselans Mar 09 '16 at 17:08
  • Thank you both for your responses. So I ran SQL Server Profiler against my database, and was able to pin down that it is specifically my stored procedures which are running slowly. The ASPNET system is fine. I'm about to take a look at the interface we are using to execute them. On a side note it seems that it's all of my stored procedures. Also my tables are all indexed. – ChettDM Mar 09 '16 at 17:35
  • @ChettM Nice! Your Stored Procedures may have and old or cached execution plans. – Esselans Mar 09 '16 at 17:59
  • Ok I got a good one for you two, and any one else reading. I found this stack over flow article http://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure After reading the answer, I updated my first stored procedure that is being run to turn ansi nulls on. That fixed my Issue... – ChettDM Mar 09 '16 at 21:22
  • So the answer to this problem was to turn on ANSI nulls in all of my stored procedures. Due to how vague my question is I'm not posting this as an answer. However the issue is resolved and I'm adding this comment for the sake of completeness. – ChettDM Mar 20 '16 at 17:45

0 Answers0