title: SQL Server Performance and Query Execution
body: <p>Few day before it was hard time..,</p>
  
  <p>I have developed an Application for Online Admission process for College students and was quite successful.</p>
  
  <p>Let me come to the problem i faced,</p>
  
  <ol>
  <li>2 tables were involved in the problem : <strong>Student_AdmissionDetails</strong> ( contains almost 30-35 fields and most of them were having datatype of nvarchar(70)) and the other one was <strong>StudentCategory</strong></li>
  <li>After few day from when admission process started, The Student_AdmissionDetails was having about 3,00,000 of records and the StudentCategory was having 4 records</li>
  <li><p>I have developed a dashboard where I was suppose to show No of Students applied in each category. and to achieve this I had following Query.</p>
  
  <p><code>Select count(*)</code><br/>
  <code>from Student_AdmissionDetails</code><br/>
  <code>inner join StudentCategory</code><br/>
  <code>on Student_AdmissionDetails.Id=StudentCategory.Id</code><br/>
  <code>where CategoryTypeName=@ParameterValue</code><br/></p></li>
  </ol>
  
  <p>The above query gets fired on single page 3 times. and There were 250-300 users who were accessing the same page simultaneously.Along with that on admission form there were 1300-2000 students were filling form at the same time.</p>
  
  <p>The problem that i get was when i ran above query in the sql server it gets fired 1 out of 5 time. <strong>It throws error that An deadlock has occurred while accessing object from memory</strong>(forgive me for not remembering the exact error).</p>
  
  <p>what i'm looking for from the following post is :</p>
  
  <ul>
  <li>This time i was bit lucky that i haven't made someone unhappy from my coding but Can anyone let me know what can be done to overcome such scenario. What can be best way to handle large DBs</li>
  <li>I tried to figure it out with SQL profiler but since there were 5 more application were running similar kind of mine i was not able to find out howmany users were trying to access the same resource.</li>
  </ul>
  
  <p>I guess following points will be helpful for answering my question.</p>
  
  <ul>
  <li>The application server and DB server different</li>
  <li>DB server was running on Windows XP(I guess!) and it was having RAM of 128 GBs</li>
  <li>When i executed query from the SQL Server it was taking average of 12-15 second to execute the query.</li>
  </ul>
  
  <p>apologize for writing this big long but i really need help to learn this :)</p>